Re: Question about timezones

On Thu, Oct 07, 2004 at 01:43:49PM -0700, Steven Klassen wrote:
> * Martijn van Oosterhout <kleptog(at)svana(dot)org> [2004-10-07 22:22:24 +0200]:
>
> > Is there any way I can use these from within postgresql? Those files
> > contains details about daylight saving changes and other useful
> > details like that, which a simple PST or EST won't cover. Or should
> > I simply do all my date/time conversion in my application?
>
> The time zone support seems pretty exhaustive. Check out section B-r
> in the document below.
>
> http://www.postgresql.org/docs/7.4/static/datetime-keywords.html

But it doesn't seem to work to actually work out times across the
world w.r.t. daylight savings.

For example, this script works out, given a time in one timezone, what
it was in another timezone:

$ sh /tmp/translatetz '2004-12-01 12:0:0' Australia/Sydney Europe/Amsterdam
Wed Dec 1 02:00:00 2004
$ sh /tmp/translatetz '2004-08-01 12:0:0' Australia/Sydney Europe/Amsterdam
Sun Aug 1 04:00:00 2004

But Brisbane doesn't have summer time, so:

$ sh /tmp/translatetz '2004-12-01 12:0:0' Australia/Brisbane Europe/Amsterdam
Wed Dec 1 03:00:00 2004

The closest I've been able to get is:

kleptog=# select timezone('MEWT',timezone('AESST','2004-12-01 12:0:0'::timestamp));
timezone
---------------------
2004-12-01 02:00:00
(1 row)

kleptog=# select timezone('MEST',timezone('AEST','2004-08-01 12:0:0'::timestamp));
timezone
---------------------
2004-08-01 04:00:00
(1 row)

In other words, if I work out myself the appropriate timezones then it
can do it. In that case I may just as well do it all myself. Mind you,
this is 7.3, would a more recent version handle this differently?

--- translatetz ---
#!/bin/sh
X=`TZ=$2 date --date="$1" +%s`
TZ=$3 perl -e 'print scalar(localtime(shift))' $X
--- snip ---
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.