On Fri, Jun 12, 2009 at 7:58 AM, James B. Byrne<byrnejb@xxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
