Re: [HACKERS] Timezone bugs

OK, tricky, but fixed --- patch attached and applied, with documentation
updates. Here is the test query:

test=> select (CURRENT_DATE + '05:00'::time)::timestamp at time zone
'Canada/Pacific';
timezone
------------------------
2005-07-22 08:00:00-04
(1 row)

I tested a bunch of others too, like:

test=> select ('2005-07-20 00:00:00'::timestamp without time zone) at
time zone 'Europe/Paris';
timezone
------------------------
2005-07-19 18:00:00-04
(1 row)

and tested that for UTC also.

It was hard to figure out how to cleanly adjust the time zone. I added
some comments explaining the process.

---------------------------------------------------------------------------

Andrew - Supernews wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073