Re: [HACKERS] Timezone bugs

Andrew pointed out that the current fix didn't handle dates that were
near daylight savings time boudaries. This handles it properly, e.g.

test=> select '2005-04-03 04:00:00'::timestamp at time zone
'America/Los_Angeles';
timezone
------------------------
2005-04-03 07:00:00-04
(1 row)

Patch attached and applied. The new fix is cleaner too.

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

pgman wrote:
>
> 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

--
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