timestamps in Australia
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ıʇǝǝɹ⅁
Jasen Betts <jasen@xnet.co.nz> writes:
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)
Yeah, we just follow the Olson timezone database here, and they
intentionally don't change the abbrevation between Aussie standard
and summer time. See the notes starting about line 650 in
src/timezone/data/australasia --- this issue is apparently of very
long standing and has been debated repeatedly.
How do the Australians handle this?
I'd go with Paul Eggert's advice in the aforementioned notes:
# Here I tend to agree with the point (most recently made by Chris
# Newman) that unique abbreviations should not be essential for proper
# operation of software. We have other instances of ambiguity
IOW, don't rely on those abbreviations to mean anything.
regards, tom lane