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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
