From: | hubert depesz lubaczewski <depesz(at)depesz(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-09 18:09:37 |
Message-ID: | 20130109180937.GB16056@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jan 08, 2013 at 04:19:59PM -0600, 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.
using partial checks (like extract, date_part, or even casting field to
date) will have problem with index usage.
the best way to handle it, is to write the parameters using date
arithmetic.
like:
where column >= '2011-01-01' and column < '2012-01-01'
do not be tempted to do:
where column >= '2011-01-01' and column <='2011-12-31'
which is very bad idea, and will cause data loss.
More on index usage:
http://www.depesz.com/2010/09/09/why-is-my-index-not-being-used/
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
From | Date | Subject | |
---|---|---|---|
Next Message | wschwurack | 2013-01-09 19:50:15 | Re: Error: absolute path not allowed |
Previous Message | Michael Nolan | 2013-01-09 17:47:59 | Re: query by partial timestamp |