timestamps in Australia

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: timestamps in Australia
Date: 2010-11-03 06:15:22
Message-ID: iaqulq$qre$1@reversiblemaps.ath.cx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


set timezone to 'Australia/Sydney';
set timezone_abbreviations to 'Australia';
set datestyle to 'SQL,DMY';

select '2011-04-03 01:00'::timestamptz+generate_series(0,3)*'1h'::interval,generate_series(0,3);

notice how the middle two look the same.
(this is Australias DST change-back)

This has the potential to cause all sorts of problems, especially in
triggers where now() seems to return a string which gets
reinterpreted...

other than setting datestyle to 'ISO,DMY' is there a way to fix this?

How do the Australians handle this?

Apparently the zic database (where the string 'EST' originates has
been this way since 2004.)

select '2011-04-03 02:00:00 EST'::timestamptz - '1h':: interval; -- invariant ?

--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dimitri Fontaine 2010-11-03 09:03:12 Re: Group by and limit
Previous Message Tom Lane 2010-11-03 03:11:36 Re: A strange SQL grammar issue with postgreSql 8.4.4 & Hibernate 3.5