Re: query by partial timestamp

On Wednesday, January 9, 2013 at 04:42, Michael Nolan wrote:

>On 1/8/13, Gavan Schneider wrote:
>
>>2.  SELECT ... WHERE
>>'2011-01-01'::TIMESTAMP <= col_of_type_timestamp
>>AND                            col_of_type_timestamp <=
>>'2011-12-31'::TIMESTAMP;
>
>This won't  quite work, because '2011-12-31'::TIMESTAMP
>is the same as 2011-12-31 00:00:00.00000
>so records timestamped later in the day on the 31st would not get selected
>
>SELECT ... WHERE
>'2011-01-01'::TIMESTAMP <= col_of_type_timestamp
>AND                 col_of_type_timestamp < '2012-01:01'::TIMESTAMP;
>
>would get all records with a 2011 timestamp.
>
Thank you. I was wondering where Tom and Depesz were coming from
when they both said less than or equal to the
'2011-12-31'::TIMESTAMP would miss data. I was giving it a rest
before re-reading, testing, and/or asking 'the right question'.
You have supplied the missing part to my puzzle.

Mostly I use DATE so have not had much practice wrestling the
TIMESTAMP edge cases. I also prefer the closed-open equality
tests as you suggest especially as they are the 'only way to go'
when grouping data on a monthly basis. My only 'defense' is that
I tried to craft my examples as close as possible to the OP
statement and not introduce the 'next year' unless forced...
lame I know. :)

Regards
Gavan Schneider