| From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
|---|---|
| To: | Kirk Wythers <kwythers(at)umn(dot)edu> |
| Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: query by partial timestamp |
| Date: | 2013-01-08 23:03:58 |
| Message-ID: | 50ECA5DE.3060103@pinpointresearch.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On 01/08/2013 02:19 PM, Kirk Wythers wrote:
> I have a column of type TIMESTAMP, I'd like to query all records from 2011. If it were text I could use a partial such as:
>
> WHERE
> text ~ '2011'
>
> There must be a simple way to pull the year part out of a timestamp format. Thanks in advance.
>
>
>
>
As others pointed out, you can do this with "extract(...).
BUT, if your timestamp column is indexed (and if the index will
constrain your records to a sufficiently small subset of the table that
use of indexes is warranted) you may be better off using date
comparisons. I doubt the planner will use the indexes otherwise.
Cheers,
Steve
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2013-01-08 23:57:06 | Re: Discerning when functions had execute revoked from public |
| Previous Message | Rob Sargent | 2013-01-08 22:51:41 | Re: query by partial timestamp |