Re: 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 = 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