Re: automatic time zone conversion

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> Hmm, postgresql knows about daylight savings if your c library knows about
> it. I'm not exactly sure how it works but you should investigate the PGTZ
> environment variables. This is what happens on my 7.2.1 system:

> select '02/06/2002 12:00:00 AEST'::timestamp;
> timestamptz
> ------------------------
> 2002-06-02 12:00:00+10
> (1 row)

> select '02/03/2002 12:00:00 AEST'::timestamp;
> timestamptz
> ------------------------
> 2002-03-02 13:00:00+11
> (1 row)

> which seems wrong to me...

Looks okay to me. Since you specified the zone in both cases, you got
the same time-of-day in GMT terms in both cases. The stored internal
form was 02:00 GMT on each date (assuming your machine thinks that AEST
is GMT+10, like mine does). That was then rotated to your own local
time zone (evidently +10/+11) for display purposes.

If you want automatic handling of summer times the correct approach is
to leave off the timezone spec on entry, whereupon PG will intuit the
correct GMT offset for your timezone rules (as set by the TimeZone
setting). For instance:

test72=# set timezone to EST5EDT;
SET VARIABLE
test72=# select '02/03/2002 12:00:00'::timestamp with time zone;
timestamptz
------------------------
2002-02-03 12:00:00-05
(1 row)

test72=# select '06/03/2002 12:00:00'::timestamp with time zone;
timestamptz
------------------------
2002-06-03 12:00:00-04
(1 row)

regards, tom lane