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.

"James B. Byrne" <byrnejb@harte-lyne.ca> 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

On Thu, Jun 11, 2009 at 2:35 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:

"James B. Byrne" <byrnejb@harte-lyne.ca> 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.

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

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/

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

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]http://www.postgresql.org/docs/current/static/functions-formatting.html:

SELECT to_char($1,'YYYYMMDD');

This is better because TEXT(dateval) doesn't have to give a string back
in the form YYYY-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

On Fri, Jun 12, 2009 at 7:58 AM, James B. Byrne<byrnejb@harte-lyne.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.