Re: search for partial dates

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.