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