Re: search for partial dates - Mailing list pgsql-general

From James B. Byrne
Subject Re: search for partial dates
Date
Msg-id 32850.216.185.71.24.1244815102.squirrel@webmail.harte-lyne.ca
Whole thread Raw
In response to Re: search for partial dates  (Andy Colson <andy@squeakycode.net>)
Responses Re: search for partial dates
List 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@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


pgsql-general by date:

Previous
From: Emanuel Calvo Franco
Date:
Subject: Re: How to store text files in the postgresql?
Next
From: Greg Stark
Date:
Subject: Re: How to store text files in the postgresql?