Here is the info: http://unicode.org/reports/tr35/tr35.html#Time_Zone_Identifiers Hmmm. Just saw a typo there ... oh, well. Every time we import the TZDB, we check the zone.tab file for changes. Mark On Fri, Feb 9, 2018 at 2:24 PM, Tobias Lindaaker <thobes@gmail.com> wrote:

Thank you Mark.

Is this the list you refer to: http://unicode.org/repos/ cldr/trunk/common/bcp47/timezone.xml ? Does this list have a stable order such as the one I described? - it looks like it is order alphabetically by the name. Could you share any details on how you compile it and keep it up to date?

Cheers, Tobias

On 9 Feb 2018, at 13:52 , Mark Davis ☕️ <mark@macchiato.com> wrote:

In CLDR we have had to produce a stable set of TZIDs as well, so you might look at that. (We also have to have short IDs, so we developed those based where possible on UNLOC codes.)

Mark

On Fri, Feb 9, 2018 at 1:44 PM, Tobias Lindaaker <thobes@gmail.com> wrote:

...

I am trying to compile a flat list of all tzids in the Time Zone Database in a way such that the position of a tzid remains the same in the list even in the face of future changes to the Time Zone Database.

I would like to know if anyone could provide me with some insight into how (a prefix of) such a list could be generated from any version of the Time Zone Database. Or would this be something I would have to maintain myself from release to release of the Time Zone Database?

The requirements I would have for generating this list would be that given two releases of the Time Zone Database, X and Y. If X is released before Y, then the list generated from X must be a prefix of the list generated from Y (and vice versa: if Y is released before X, the list generated from Y must be a prefix of the list generated from X).

A simple alphabetical ordering is obviously not going to work, since for example the addition of America/Punta_Arenas in 2017a would end up before many of the tzids that existed in versions prior.

Since I don't require this to work for all historical releases, my first thought was to start from a fairly recent release (tzdata2013h) and verify that I got the same order when sorting the timezones based on their earliest rule. However the removal of tzids (such as Canada/East-Saskatchewan in tzdata2017c) made this impossible, since any removal makes it impossible to ensure that prior versions are strict prefixes.

It now seems as if I would have to maintain this list myself, tracking each release of the Time Zone Database and ensure that any tzids added to the Time Zone Database are appended to my list in release order. If possible I would very much prefer to avoid this, since it requires me to release updates of my software to track the releases of the Time Zone Database, I would much rather like it if my software could rely on the Time Zone Database available from the system where it was running.

Finally some background on why (I think) I need to generate this type of list of tzids: I am improving the support for storing timestamp values in a database management system (Neo4j), and would like to avoid having to store the timezone component of zoned timestamps as a (variable length) text component, but rather store it as a fixed size integer offset into a table of tzids. Thus I need such a stable list of tzids.

I have tried searching the archives of this mailing list for similar questions before posting, but have not been able to find such an entry. The closest I found were a few questions on how to translate the tzids to localized names. Since I did not read through the entire archives, but only did a search, I apologise in advance if this has been asked and solved before, and hope that you will be able to direct my attention to that thread.

Thank you, Tobias

Condensing my reply to two people into one email, in order to not generate too much traffic on the list.

On 9 Feb 2018, at 14:54 , Philip Paeps <philip@trouble.is> wrote:

On 2018-02-09 13:44:03 (+0100), Tobias Lindaaker wrote:

...

I am trying to compile a flat list of all tzids in the Time Zone Database in a way such that the position of a tzid remains the same in the list even in the face of future changes to the Time Zone Database.

I would like to know if anyone could provide me with some insight into how (a prefix of) such a list could be generated from any version of the Time Zone Database. Or would this be something I would have to maintain myself from release to release of the Time Zone Database?

I think this is something you'll have to maintain yourself. As you discovered, timezones sometimes get deleted so you'll need to be aware of the data files and the backzone file.

And in the case of tzids such as Asia/Alma-Ata, Asia/Ishigaki, and Canada/East-Saskatchewan I need to just manually keep track of them because they were completely removed from the tz database.

...

Finally some background on why (I think) I need to generate this type of list of tzids: I am improving the support for storing timestamp values in a database management system (Neo4j), and would like to avoid having to store the timezone component of zoned timestamps as a (variable length) text component, but rather store it as a fixed size integer offset into a table of tzids. Thus I need such a stable list of tzids.

I wonder why you're storing timezone information in the database? Is there any reason you can't simply store UTC timestamps and show them in the correct timezone in the presentation layer?

I do store UTC timestamps, but I also store the origin time zone in order to be able to show the timestamp correctly in the presentation layer. I don't need to store any of the definition of the time zones, only the tzid itself. I completely rely on the tzdb for being able to convert the UTC timestamp to the correct local time for the zone that the timestamp originated from.

On 9 Feb 2018, at 19:26 , Paul.Koning@dell.com wrote:

...

On Feb 9, 2018, at 1:23 PM, Paul Koning <paul.koning@dell.com> wrote:

...

On Feb 9, 2018, at 7:44 AM, Tobias Lindaaker <thobes@gmail.com> wrote:

I am trying to compile a flat list of all tzids in the Time Zone Database in a way such that the position of a tzid remains the same in the list even in the face of future changes to the Time Zone Database.

Isn't this already in place? Timezones are identified by <continent>/<city>. While the primary name for a zone may change, for example if the "best known" spelling of the city changes, the previous name in such cases is preserved as a link.

So you can read the current tzdata to find all the names. Those that aren't links are today's TZ identifiers. In the future, if a new name shows up, it's either a renamed zone (if a previously known zone name is now a link to that new name) or a newly defined zone (if no such link exists).

If that isn't sufficient, what's missing?

Oh, I missed something. You want them sorted and for the positions to remain fixed. That's clearly not possible. And why do you need that? The strings are stable identifiers as I described. If you don't like keying lots of records by strings, which is reasonable, you can create an auxiliary table that maps the strings into small integers which are then used in the other tables. You can do all this today.

Yes. What I am asking for is if somebody already had such an auxiliary table defined somewhere, or if anyone had hints on the best strategy for creating such an auxiliary table. My original plan was to dynamically generate something similar to a system table for mapping between the string identifiers and small integers. For various technical reasons that I don't think I need to bore this list with a number of my colleagues were against this idea and proposed instead generating a static mapping table and ship it as part our code. After some investigation into how to best generate such a mapping table in a stable way, which ended with me bumping into the removal of Canada/East-Saskatchewan (which meant it would be impossible to generate a stable list based on only a single release of tzdata), I decided to ask this list for input. I would still very much like it if I could maintain a dynamic mapping based on the tzids in actual use rather than a static pre-generated table, but in order for the feature to make the next release window I need to at least investigate both options in parallel. To be honest, the static-table-in-code approach is not looking as bad as I first thought, even if I do have to continuously track new tzdata releases, in particular because I would need to do some of that anyhow in order to handle things like Canada/East-Saskatchewan. Cheers, Tobias

On Feb 9, 2018, at 1:23 PM, Paul Koning <paul.koning@dell.com> wrote:

...

On Feb 9, 2018, at 7:44 AM, Tobias Lindaaker <thobes@gmail.com> wrote:

I am trying to compile a flat list of all tzids in the Time Zone Database in a way such that the position of a tzid remains the same in the list even in the face of future changes to the Time Zone Database.

Isn't this already in place? Timezones are identified by <continent>/<city>. While the primary name for a zone may change, for example if the "best known" spelling of the city changes, the previous name in such cases is preserved as a link.

So you can read the current tzdata to find all the names. Those that aren't links are today's TZ identifiers. In the future, if a new name shows up, it's either a renamed zone (if a previously known zone name is now a link to that new name) or a newly defined zone (if no such link exists).

If that isn't sufficient, what's missing?

Oh, I missed something. You want them sorted and for the positions to remain fixed. That's clearly not possible. And why do you need that? The strings are stable identifiers as I described. If you don't like keying lots of records by strings, which is reasonable, you can create an auxiliary table that maps the strings into small integers which are then used in the other tables. You can do all this today. paul

Thank you Stephen, That sounds very similar to what I want to achieve. Storing timestamps in UTC but tagging with origin zone is exactly what I am doing as well. The C enum you describe, and the desire to use a fixed size small integer in storage is indeed the same as in my use case. The changes you describe having made to zic don't quite sound like they would be useful to me, but I am curious to hear about what strategy you used for generating the enum. Did you do this manually, or using some script? What strategy do you have for keeping it up to date with new releases of tzdata? Cheers, Tobias

On 12 Feb 2018, at 13:59 , Goudge, Stephen <stephen.goudge@petards.com> wrote:

...

-----Original Message----- From: tz [mailto:tz-bounces@iana.org] On Behalf Of Tobias Lindaaker Sent: 09 February 2018 12:44 I am trying to compile a flat list of all tzids in the Time Zone Database in a way such that the position of a tzid remains the same in the list even in the face of future changes to the Time Zone Database.

<snip>

I have tried searching the archives of this mailing list for similar questions before posting, but have not been able to find such an entry.

Back in 2008 I posted here that I am doing pretty much exactly the same thing as requested here: basically, I have a C enum that lists out all the zones and as new ones are created this gets extended. A simple table, indexed on that enum, converts to the string name, such as "America/New_York". This has to be manually updated as new names are added. This is (still) being used to allow timestamps to be stored in UTC but tagged with their origin zone - in a space-efficient fashion for an embedded system - so that we can display UTC or the correct local time in a user interface.

Original post: https://mm.icann.org/pipermail/tz/2008-January/014801.html

As there was zero interest shown in this at the time, I've still not yet bothered to clean it up, get a GitHub account and post it, but I'm quite willing to do so if there is any interest these days (though the amount of clean-up done will start at - none!)

Regards,

Stephen Goudge Senior Software Engineer

Petards Joyce-Loebl Limited 390 Princesway Team Valley Gateshead Tyne & Wear NE11 0TU

T +44 (0) 191 420 3015 F +44 (0) 191 420 3030 W www.petards.com

___________________________________________________________________________________________

This email has been scanned by Petards. The service is powered by Symantec MessageLabs Email AntiVirus.cloud ___________________________________________________________________________________________

This email has been sent from Petards Group plc or a member of the Petards group of companies. The information in this email is confidential and/or privileged. It is intended solely for the use of the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any review, dissemination, disclosure, alteration, printing, circulation or transmission of this e-mail and/or any file or attachment transmitted with it, is prohibited and may be unlawful.

Petards Group plc is registered in England & Wales. Company No. 2990100 ___________________________________________________________________________________________

On 14 Feb 2018, at 04:43 , Clive D.W. Feather <clive@davros.org> wrote:

Goudge, Stephen said:

...

Back in 2008 I posted here that I am doing pretty much exactly the same thing as requested here: basically, I have a C enum that lists out all the zones and as new ones are created this gets extended.

While I appreciate what you're doing and why, I would prefer that the database allocated a unique persistent random number (four or five digits, or even six with a simple check code) to each zone.

If an official allocation of that type was made, that would certainly fit my use cases.

We discussed this a year ago, but from memory it got bogged down in proposals to make the numbers match alphabetical order of names or something like that.

Do you have an archive link to that discussion. I would be interested in what the arguments were for that. In my opinion it would make the numbers easier to allocate if we did not have to care about preserving a particular order, so I am curious to learn what the arguments for alphabetical order were. Assigning numbers for tzids certainly seems to be within the scope of what the Internet Assigned Numbers Authority could be doing... ;-) Cheers, Tobias

Tobias Lindaaker wrote:

...

On 14 Feb 2018, at 04:43 , Clive D.W. Feather <clive@davros.org> wrote: We discussed this a year ago, but from memory it got bogged down in proposals to make the numbers match alphabetical order of names or something like that.

Do you have an archive link to that discussion. I would be interested in what the arguments were for that.

It looks to be messages from February 2017 under the Subject lines: Version in zoneinfo files? Zone ID in binary (was Re: Version in zoneinfo files?) ID string in zic's output files Representative messages are: http://mm.icann.org/pipermail/tz/2017-February/024812.html http://mm.icann.org/pipermail/tz/2017-February/024822.html http://mm.icann.org/pipermail/tz/2017-February/024838.html http://mm.icann.org/pipermail/tz/2017-February/024830.html http://mm.icann.org/pipermail/tz/2017-February/024825.html http://mm.icann.org/pipermail/tz/2017-February/024861.html

In my opinion it would make the numbers easier to allocate if we did not have to care about preserving a particular order, so I am curious to learn what the arguments for alphabetical order were.

I don't see any suggestions (in the threads I cited) to keep them ordered. The big question seemed to be where to store them in the zoneinfo files. There are 15 unused bytes available in the current format, but it wasn't clear those were enough. On that note, I would like to advocate *not* burning all 15 of those bytes on this (or any) proposal! Who knows what else we might need them for. I'd like to use, say, six of those bytes for the offset and size of a new table of key/value pairs, which could include version number, original zone name, and all sorts of other metadata and commentary. For zoneinfo readers I'm familiar with, this might not even require a version number bump, as the new data would appear to be unnecessary binary garbage at the end, and (hopefully) ignored.