Re: search for partial dates

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.