From: | Gavan Schneider <pg-gts(at)snkmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: query by partial timestamp |
Date: | 2013-01-10 11:11:56 |
Message-ID: | 14069-1357816319-622070@sneakemail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Kroon | 2013-01-10 14:36:32 | Identifier gets truncated |
Previous Message | aasat | 2013-01-10 10:04:54 | Re: Bug: dblink_send_query not work on 9.2? |