Re: Aussie timezone database changes incoming

In connection with a question asked today on pgsql-general, I had
occasion to go check the release announcements for the IANA timezone
database files, and it turns out that there are some big changes in
2014f:
http://mm.icann.org/pipermail/tz-announce/2014-August/000023.html

The Russian changes are perhaps not such a big deal because they've
done that sort of thing before, but this is an earful:

Australian eastern time zone abbreviations are now AEST/AEDT not
EST, and similarly for the other Australian zones. That is, for
eastern standard and daylight saving time the abbreviations are AEST
and AEDT instead of the former EST for both; similarly, ACST/ACDT,
ACWST/ACWDT, and AWST/AWDT are now used instead of the former CST,
CWST, and WST. This change does not affect UTC offsets, only time
zone abbreviations. (Thanks to Rich Tibbett and many others.)

I'm wondering how many Aussie applications are going to break when
this goes in, and if we could/should do anything about it. One idea
that comes to mind is to create an "Australia_old" tznames file
containing the current Aussie zone abbreviations, so as to provide
an easy way to maintain backwards compatibility at need (you'd select
that as your timezone_abbreviations GUC setting).

Anyone from down under care to remark about the actual usage of old
and new abbreviations?

regards, tom lane

On 09/10/2014 11:23 PM, Tom Lane wrote:
> In connection with a question asked today on pgsql-general, I had
> occasion to go check the release announcements for the IANA timezone
> database files, and it turns out that there are some big changes in
> 2014f:
> http://mm.icann.org/pipermail/tz-announce/2014-August/000023.html
>
> The Russian changes are perhaps not such a big deal because they've
> done that sort of thing before, but this is an earful:
>
> Australian eastern time zone abbreviations are now AEST/AEDT not
> EST, and similarly for the other Australian zones. That is, for
> eastern standard and daylight saving time the abbreviations are AEST
> and AEDT instead of the former EST for both; similarly, ACST/ACDT,
> ACWST/ACWDT, and AWST/AWDT are now used instead of the former CST,
> CWST, and WST. This change does not affect UTC offsets, only time
> zone abbreviations. (Thanks to Rich Tibbett and many others.)

Oh, lovely.

I shouldn't be surprised that Australia gets to change. While the cynic
in me thinks this is the usual USA-is-the-center-of-the-universe-ism, in
reality it makes sense given relative population and likely impact.

> I'm wondering how many Aussie applications are going to break when
> this goes in, and if we could/should do anything about it. One idea
> that comes to mind is to create an "Australia_old" tznames file
> containing the current Aussie zone abbreviations, so as to provide
> an easy way to maintain backwards compatibility at need (you'd select
> that as your timezone_abbreviations GUC setting).
>
> Anyone from down under care to remark about the actual usage of old
> and new abbreviations?

Most systems I see work in UTC, but I don't actually work with many
that're in Australia.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

>The Russian changes are perhaps not such a big deal because they've
>done that sort of thing before, but this is an earful:
>
> Australian eastern time zone abbreviations are now AEST/AEDT not
> EST, and similarly for the other Australian zones. That is, for
> eastern standard and daylight saving time the abbreviations are AEST
> and AEDT instead of the former EST for both; similarly, ACST/ACDT,
> ACWST/ACWDT, and AWST/AWDT are now used instead of the former CST,
> CWST, and WST. This change does not affect UTC offsets, only time
> zone abbreviations. (Thanks to Rich Tibbett and many others.)
[...]
>Anyone from down under care to remark about the actual usage of old
>and new abbreviations?

AEST/AEDT/etc are the official abbreviations and are commonly used.
They have been increasingly used over the last 20 years or so, and the
EST/EDT stuff on the Olsen tz database has been a source of annoyance
for a very long time, eg:

http://thread.gmane.org/gmane.comp.time.tz/2262

Quite likely this change will break stuff, but my feeling is more people
will be cheering than screaming.

--
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/

On Thu, Sep 11, 2014 at 12:20 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> I shouldn't be surprised that Australia gets to change. While the cynic
> in me thinks this is the usual USA-is-the-center-of-the-universe-ism, in
> reality it makes sense given relative population and likely impact.

Just because it makes sense doesn't mean it isn't
USA-is-the-center-of-the-universe-ism.

...Robert

On 12/09/14 01:57, Robert Haas wrote:
> On Thu, Sep 11, 2014 at 12:20 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>> I shouldn't be surprised that Australia gets to change. While the cynic
>> in me thinks this is the usual USA-is-the-center-of-the-universe-ism, in
>> reality it makes sense given relative population and likely impact.
> Just because it makes sense doesn't mean it isn't
> USA-is-the-center-of-the-universe-ism.
>
> ...Robert
>
>
In the same way the Americans tend to act like they are not on a planet,
by saying something will happen in Summer - completely ignoring that for
people who live in the Southern hemisphere, for whom that will be Winter!

Cheers,
Gavin

On Thu, Sep 11, 2014 at 03:42:14PM +1000, Andrew McNamara wrote:
> >Anyone from down under care to remark about the actual usage of old
> >and new abbreviations?

About bloody time!

>
> AEST/AEDT/etc are the official abbreviations and are commonly used.
> They have been increasingly used over the last 20 years or so, and the
> EST/EDT stuff on the Olsen tz database has been a source of annoyance
> for a very long time, eg:
>
> http://thread.gmane.org/gmane.comp.time.tz/2262
>
> Quite likely this change will break stuff, but my feeling is more people
> will be cheering than screaming.

Indeed, this has been a pain in the ass for a long long time.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Thu, Sep 11, 2014 at 03:42:14PM +1000, Andrew McNamara wrote:
>> Quite likely this change will break stuff, but my feeling is more people
>> will be cheering than screaming.

> Indeed, this has been a pain in the ass for a long long time.

It's good news that people think this will be an improvement.

I've not dug into the change details to be sure, but I think probably
I was overthinking it upthread. We seem to already have some of the
new abbreviations installed, and the other ones do not conflict with
anything. So we'll just add them and be happy. What we should do
with the "Australia" abbreviations file is re-document it as being
appropriate for historical usage only. Anyone who's got that
selected will continue to see the behavior they did before.

regards, tom lane

I wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>> Indeed, this has been a pain in the ass for a long long time.

> It's good news that people think this will be an improvement.

> I've not dug into the change details to be sure, but I think probably
> I was overthinking it upthread. We seem to already have some of the
> new abbreviations installed, and the other ones do not conflict with
> anything. So we'll just add them and be happy. What we should do
> with the "Australia" abbreviations file is re-document it as being
> appropriate for historical usage only. Anyone who's got that
> selected will continue to see the behavior they did before.

I've committed changes for this in advance of the upcoming 9.4beta3
release. Hopefully, if this is seriously bad for anyone, we'll hear
about it from beta testers before it gets into any official back-branch
releases.

regards, tom lane

On Sat, Oct 4, 2014 at 02:21:24PM -0400, Tom Lane wrote:
> I wrote:
> > Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> >> Indeed, this has been a pain in the ass for a long long time.
>
> > It's good news that people think this will be an improvement.
>
> > I've not dug into the change details to be sure, but I think probably
> > I was overthinking it upthread. We seem to already have some of the
> > new abbreviations installed, and the other ones do not conflict with
> > anything. So we'll just add them and be happy. What we should do
> > with the "Australia" abbreviations file is re-document it as being
> > appropriate for historical usage only. Anyone who's got that
> > selected will continue to see the behavior they did before.
>
> I've committed changes for this in advance of the upcoming 9.4beta3
> release. Hopefully, if this is seriously bad for anyone, we'll hear
> about it from beta testers before it gets into any official back-branch
> releases.

The changes for the Russian Federation timezones taking effect October
26 reinforces our need to get a new set of minor releases out soon. In
fact, those storing future dates might already need those updates.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On 10/4/14, 2:58 PM, Bruce Momjian wrote:
>> I've committed changes for this in advance of the upcoming 9.4beta3
>> >release. Hopefully, if this is seriously bad for anyone, we'll hear
>> >about it from beta testers before it gets into any official back-branch
>> >releases.
> The changes for the Russian Federation timezones taking effect October
> 26 reinforces our need to get a new set of minor releases out soon. In
> fact, those storing future dates might already need those updates.
This is why I wish we had a data type that stored the timezone that was originally in effect. :/

On Sat, Oct 4, 2014 at 03:01:45PM -0500, Jim Nasby wrote:
> On 10/4/14, 2:58 PM, Bruce Momjian wrote:
> >>I've committed changes for this in advance of the upcoming 9.4beta3
> >>>release. Hopefully, if this is seriously bad for anyone, we'll hear
> >>>about it from beta testers before it gets into any official back-branch
> >>>releases.
> >The changes for the Russian Federation timezones taking effect October
> >26 reinforces our need to get a new set of minor releases out soon. In
> >fact, those storing future dates might already need those updates.
> This is why I wish we had a data type that stored the timezone that was originally in effect. :/

Uh, if we stored the _offset_ that was in effect at the time of storage,
it would actually be _worse_ because the new timezone database would not
adjust existing stored values. If we stored the name of the time zone,
I am not sure how that would help us here.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Sat, Oct 4, 2014 at 02:21:24PM -0400, Tom Lane wrote:
>> I've committed changes for this in advance of the upcoming 9.4beta3
>> release. Hopefully, if this is seriously bad for anyone, we'll hear
>> about it from beta testers before it gets into any official back-branch
>> releases.

> The changes for the Russian Federation timezones taking effect October
> 26 reinforces our need to get a new set of minor releases out soon. In
> fact, those storing future dates might already need those updates.

Well, the other side of that coin is that those new abbreviation values
aren't valid *yet*.

It's becoming clear to me that our existing design whereby zone
abbreviations represent fixed GMT offsets isn't really good enough.
I've been wondering whether we could change things so that, for instance,
"EDT" means "daylight time according to America/New_York" and the system
would consult the zic database to find out what the prevailing GMT offset
was in that zone on that date. This would be a lot more robust in the
face of the kind of foolishness we now see actually goes on.

regards, tom lane

On Sat, Oct 4, 2014 at 05:03:24PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > On Sat, Oct 4, 2014 at 02:21:24PM -0400, Tom Lane wrote:
> >> I've committed changes for this in advance of the upcoming 9.4beta3
> >> release. Hopefully, if this is seriously bad for anyone, we'll hear
> >> about it from beta testers before it gets into any official back-branch
> >> releases.
>
> > The changes for the Russian Federation timezones taking effect October
> > 26 reinforces our need to get a new set of minor releases out soon. In
> > fact, those storing future dates might already need those updates.
>
> Well, the other side of that coin is that those new abbreviation values
> aren't valid *yet*.
>
> It's becoming clear to me that our existing design whereby zone
> abbreviations represent fixed GMT offsets isn't really good enough.
> I've been wondering whether we could change things so that, for instance,
> "EDT" means "daylight time according to America/New_York" and the system
> would consult the zic database to find out what the prevailing GMT offset
> was in that zone on that date. This would be a lot more robust in the
> face of the kind of foolishness we now see actually goes on.

I see:

SET timezone = 'GMT';

SELECT '1901-01-01 00:00:00 EDT'::timestamptz;
timestamptz
------------------------
1901-01-01 04:00:00+00

SELECT '1901-01-01 00:00:00 EST'::timestamptz;
timestamptz
------------------------
1901-01-01 05:00:00+00

This is returning adjustements for EDT in a year when there was not
daylight savings time.

How are Russians supposed to deploy Postgres on October 26 if they use
abbeviations? At midnight?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> How are Russians supposed to deploy Postgres on October 26 if they use
> abbeviations? At midnight?

Pretty much. The only bright spot is that the tznames files are just
text and can be edited easily, so you can change them when you need to.

This isn't the first time this has happened, of course; in fact we
are pretty much reversing changes made in commit 3b91fe185a71c05a.
And that was in response to law changes that had happened two years
before (and nobody had complained meanwhile). So I'm not prepared to
consider this a critical issue.

regards, tom lane

On 10/4/14, 3:25 PM, Bruce Momjian wrote:
> On Sat, Oct 4, 2014 at 03:01:45PM -0500, Jim Nasby wrote:
>> On 10/4/14, 2:58 PM, Bruce Momjian wrote:
>>>> I've committed changes for this in advance of the upcoming 9.4beta3
>>>>> release. Hopefully, if this is seriously bad for anyone, we'll hear
>>>>> about it from beta testers before it gets into any official back-branch
>>>>> releases.
>>> The changes for the Russian Federation timezones taking effect October
>>> 26 reinforces our need to get a new set of minor releases out soon. In
>>> fact, those storing future dates might already need those updates.
>> This is why I wish we had a data type that stored the timezone that was originally in effect. :/
> Uh, if we stored the _offset_ that was in effect at the time of storage,
Oh heck no. You NEVER want to use offsets instead of real timezones (something that far too many programmers don't seem to understand).
> it would actually be _worse_ because the new timezone database would not
> adjust existing stored values. If we stored the name of the time zone,
> I am not sure how that would help us here.
>
If we stored the name of the timezone then updates to the TZ database would take effect when the data was read back. Of course that doesn't help with indexes, but at least you can reindex (and I don't think it'd be to hard to be more clever than a bulk reindex).

Aside from that, I don't like that we throw away information (namely, what timezone was used when the record was written). If we stored the timezone you could actually find that out when you read the data back.