Lists: | pgsql-general |
---|
From: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | search for partial dates |
Date: | 2009-06-11 20:23:30 |
Message-ID: | 45408.216.185.71.24.1244751810.squirrel@webmail.harte-lyne.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Given a datetime column, not null, is there a single syntax that
permits searching for all dates in a given year, year+month, and
year+month+day such that a single parameterised query can handle all
three circumstances?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
From: | Christophe <xof(at)thebuild(dot)com> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: search for partial dates |
Date: | 2009-06-11 20:30:15 |
Message-ID: | 6FFF18D5-00D8-4015-ACA5-9D01F82C3DC9@thebuild.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Jun 11, 2009, at 1:23 PM, James B. Byrne wrote:
> Given a datetime column, not null, is there a single syntax that
> permits searching for all dates in a given year, year+month, and
> year+month+day such that a single parameterised query can handle all
> three circumstances?
Well, of course, in a trivial sense:
SELECT * FROM the_table WHERE datetimecolumn >= $1 AND datetimecolumn
<= $2;
The application has to create the appropriate values for the first and
last days of the year or month in this case, but it's a rare language
that doesn't that facility.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: search for partial dates |
Date: | 2009-06-11 20:35:20 |
Message-ID: | 18768.1244752520@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"James B. Byrne" <byrnejb(at)harte-lyne(dot)ca> writes:
> Given a datetime column, not null, is there a single syntax that
> permits searching for all dates in a given year, year+month, and
> year+month+day such that a single parameterised query can handle all
> three circumstances?
Try date_trunc() ... however, if you want the query to be indexable,
it'll take a bit more work.
regards, tom lane
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: search for partial dates |
Date: | 2009-06-11 20:51:00 |
Message-ID: | dcc563d10906111351s578191d8w42634e9ca9a32901@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, Jun 11, 2009 at 2:35 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "James B. Byrne" <byrnejb(at)harte-lyne(dot)ca> writes:
>> Given a datetime column, not null, is there a single syntax that
>> permits searching for all dates in a given year, year+month, and
>> year+month+day such that a single parameterised query can handle all
>> three circumstances?
Given the use of the name datetime I'm gonna guess OP is coming from
MySQL. In MySQL you'd have a function sort of like
date(timestampfield) etc to do this.
> Try date_trunc() ... however, if you want the query to be indexable,
> it'll take a bit more work.
Note that for reporting databases it's pretty common to create indexes
on the most common and selective of date_trunc(timestamp), which will
then make them indexable. note that it's also pretty easy to create
your own trunc function that divides up the day by 5 or 10 or 30
minute intervals and index on that.
From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca> |
Subject: | Re: search for partial dates |
Date: | 2009-06-11 21:37:28 |
Message-ID: | 4A317918.7050105@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
James B. Byrne wrote:
> Given a datetime column, not null, is there a single syntax that
> permits searching for all dates in a given year, year+month, and
> year+month+day such that a single parameterised query can handle all
> three circumstances?
>
That's a little vague, so how about:
select * from somethine where (extract(year from idate) = $1) or
(extract(year from idate) = $2 and extract(month from idate) = $3) or
(extract(year from idate) = $4 and extract(month from idate) = $5 and
extract(day from idate) = $6)
-Andy
From: | "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: search for partial dates |
Date: | 2009-06-11 22:47:26 |
Message-ID: | 200906120047.26529.leif@solumslekt.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thursday 11. June 2009, James B. Byrne wrote:
>Given a datetime column, not null, is there a single syntax that
>permits searching for all dates in a given year, year+month, and
>year+month+day such that a single parameterised query can handle all
>three circumstances?
Apart from the other excellent replies you've got, you can always do
some explicit casting and produce interesting things like:
CREATE OR REPLACE FUNCTION date2text(DATE) RETURNS TEXT AS $$
-- removes hyphens from a regular date
SELECT
SUBSTR(TEXT($1),1,4) ||
SUBSTR(TEXT($1),6,2) ||
SUBSTR(TEXT($1),9,2)
$$ LANGUAGE sql STABLE;
for example.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
From: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca> |
---|---|
To: | "Andy Colson" <andy(at)squeakycode(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: search for partial dates |
Date: | 2009-06-12 13:58:22 |
Message-ID: | 32850.216.185.71.24.1244815102.squirrel@webmail.harte-lyne.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, June 11, 2009 17:37, Andy Colson wrote:
> That's a little vague, so how about:
>
> select * from somethine where (extract(year from idate) = $1) or
> (extract(year from idate) = $2 and extract(month from idate) = $3)
> or (extract(year from idate) = $4 and extract(month from idate) = $5
> and extract(day from idate) = $6)
>
Actually, I am thinking that perhaps this is better accomplished by
parsing the data in the application and generating a date range that
I then pass as parameters to a PG BETWEEN condition:
For example:
given 2008 then SD = 20080101000001 and ED = 20081231235959
given 200805 then SD = 20080501000001 and ED = 20080531235959
given 20080709 then SD = 20080709000001 and ED = 20080709235959
I believe that this construction should work and also make use of
the index
SELECT * WHERE effective_from BETWEEN SD and ED
Is my appreciate correct?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: search for partial dates |
Date: | 2009-06-12 18:38:44 |
Message-ID: | 20090612183844.GY5407@samason.me.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Jun 12, 2009 at 12:47:26AM +0200, Leif B. Kristensen wrote:
> CREATE OR REPLACE FUNCTION date2text(DATE) RETURNS TEXT AS $$
> -- removes hyphens from a regular date
> SELECT
> SUBSTR(TEXT($1),1,4) ||
> SUBSTR(TEXT($1),6,2) ||
> SUBSTR(TEXT($1),9,2)
> $$ LANGUAGE sql STABLE;
Why not use the to_char function[1]:
SELECT to_char($1,'YYYYMMDD');
This is better because TEXT(dateval) doesn't have to give a string back
in the form YYYY-MM-DD, it just does by default. Readability also seems
to improve when using to_char.
--
Sam http://samason.me.uk/
[1] http://www.postgresql.org/docs/current/static/functions-formatting.html
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca> |
Cc: | Andy Colson <andy(at)squeakycode(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: search for partial dates |
Date: | 2009-06-12 20:25:17 |
Message-ID: | dcc563d10906121325n296519fkf858223500a9f0f6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Jun 12, 2009 at 7:58 AM, James B. Byrne<byrnejb(at)harte-lyne(dot)ca> wrote:
>
> On Thu, June 11, 2009 17:37, Andy Colson wrote:
>
>> That's a little vague, so how about:
>>
>> select * from somethine where (extract(year from idate) = $1) or
>> (extract(year from idate) = $2 and extract(month from idate) = $3)
>> or (extract(year from idate) = $4 and extract(month from idate) = $5
>> and extract(day from idate) = $6)
>>
>
> Actually, I am thinking that perhaps this is better accomplished by
> parsing the data in the application and generating a date range that
> I then pass as parameters to a PG BETWEEN condition:
>
> For example:
>
> given 2008 then SD = 20080101000001 and ED = 20081231235959
>
> given 200805 then SD = 20080501000001 and ED = 20080531235959
>
> given 20080709 then SD = 20080709000001 and ED = 20080709235959
>
> I believe that this construction should work and also make use of
> the index
>
> SELECT * WHERE effective_from BETWEEN SD and ED
>
>
> Is my appreciate correct?
Yeah, if you're just looking at a where clause, between or
where tsfield >= '2008-07-09 00:00:00' and tsfield < '2008-07-10 00:00:00'
is even easier to code up, and you won't miss the rare time with
timestamp precision of '2008-07-09 23:59:59.456204' or whatnot.
The date_trunc and custom trunc functions come in handy when you want
to group by time increments like 5 minutes etc.