From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: search for partial dates |
Date: | 2009-06-11 20:51:00 |
Message-ID: | dcc563d10906111351s578191d8w42634e9ca9a32901@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 11, 2009 at 2:35 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "James B. Byrne" <byrnejb(at)harte-lyne(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-06-11 20:53:42 | Re: listing relations |
Previous Message | Brandon Metcalf | 2009-06-11 20:49:35 | Re: listing relations |