Re: Timezone bugs

On 2005-07-22, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>> 
>> select (CURRENT_DATE + '05:00'::time)::timestamp
>>    at time zone 'Canada/Pacific';
>>         timezone        
>> ------------------------
>>  2005-07-19 22:00:00+00
>> (1 row)
>> 
> What is happening here is that 2005-07-20 05:00:00 is being cast back 7
> hours (Canada/Pacific offset), and that is 22:00 of the previous day.

Which is of course completely wrong.

Let's look at what should happen:
(date + time) = timestamp without time zone

'2005-07-20' + '05:00' = '2005-07-20 05:00:00'::timestamp
(timestamp without time zone) AT TIME ZONE 'zone'

When AT TIME ZONE is applied to a timestamp without time zone, it is
supposed to keep the _same_ calendar time and return a result of type
timestamp with time zone designating the absolute time. So in this case,
we expect the following to happen:
'2005-07-20 05:00:00'              (original timestamp)-> '2005-07-20 05:00:00-0700'      (same calendar time in new
zone)->'2005-07-20 12:00:00+0000'      (convert to client timezone (UTC))
 

So the conversion is being done backwards, resulting in the wrong result.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services