Re: [GENERAL] search for partial dates
On Fri, Jun 12, 2009 at 7:58 AM, James B. Byrne 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 = 2008010101 and ED = 20081231235959 > > given 200805 then SD = 2008050101 and ED = 20080531235959 > > given 20080709 then SD = 2008070901 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
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,'MMDD'); This is better because TEXT(dateval) doesn't have to give a string back in the form -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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
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 = 2008010101 and ED = 20081231235959 given 200805 then SD = 2008050101 and ED = 20080531235959 given 20080709 then SD = 2008070901 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. Byrnemailto:byrn...@harte-lyne.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
On Thu, Jun 11, 2009 at 2:35 PM, Tom Lane wrote: > "James B. Byrne" 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
"James B. Byrne" 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] search for partial dates
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. Byrnemailto:byrn...@harte-lyne.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
