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
