On Thu, Jun 11, 2009 at 2:35 PM, Tom Lane<tgl@xxxxxxxxxxxxx> wrote: > "James B. Byrne" <byrnejb@xxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
