Re: Timezone bugs

On 2005-07-22, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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