Re: query by partial timestamp

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

In response to

Browse pgsql-general by date

  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?