Re: query by partial timestamp

On Tuesday, January 8, 2013 at 09:26, Raymond O'Donnell wrote:

>On 08/01/2013 22:19, 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.
>
>You want the extract() function.
>
From my perspective there are at least three ways to attack
this problem:

(I have not tested these, so apologies for the stupid syntax errors.)

1. SELECT ... WHERE 2011 = extract(YEAR FROM col_of_type_timestamp);

2. SELECT ... WHERE
'2011-01-01'::TIMESTAMP <= col_of_type_timestamp
AND col_of_type_timestamp <= '2011-12-31'::TIMESTAMP;

3. SELECT ... WHERE
(col_of_type_timestamp, col_of_type_timestamp) OVERLAPS
(DATE '2011-01-01', DATE '2012-01-01');

Is this the full list?

So... generalizing the original question: which approach would
yield the best performance and/or compliance with SQL standards?

I note Steve Crawford has (strongly) hinted that direct date
comparison is more likely to use an index (when available) so I
suspect this is the way to go, but would an index based on
extract(YEAR...) negate this difference?

Regards
Gavan Schneider