Re: query by partial timestamp

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
suchas: 
>
> 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