Re: query by partial timestamp

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/

In response to

Browse pgsql-general by date

  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