On Thu, Oct 07, 2004 at 01:43:49PM -0700, Steven Klassen wrote:
> * Martijn van Oosterhout <kleptog@svana.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@svana.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.
