How extract cldr timezones from xml file to store in a DB table? In our DB, I need each location to be associated with a "timezone".

These timezones are in https://github.com/unicode-org/cldr/blob/release-36-1/common/supplemental/metaZones.xml

I want to make a table of the timezone name strings in that xml file, so that we will have a "timezone table ID" that I can send as part of location data to client software. The client contains the code that uses timezone to determine time offset (e.g. UTC+1) for a given datetime at that location.

That is, instead of storing/sending/parsing timezone strings, for some (internal) purposes we will use this table ID.



How can I extract the list of timezone name strings from that xml file, and store it in a DB table? (Obviously I could write a little program to do this, but someone must have done this already.)

Or perhaps somewhere else in each cldr release, there is a (plaintext) list of those timezones?

Or CSV formatted info, that would be easier to massage manually.
Partial answer: how to convert the relevant info to csv.

Edit the file to remove everything except this section:

&lt;metazoneInfo&gt;
    &lt;timezone type="Africa/Abidjan"&gt;
        &lt;usesMetazone mzone="GMT"/&gt;
    &lt;/timezone&gt;
...
    &lt;timezone type="PST8PDT"&gt;
        &lt;usesMetazone mzone="America_Pacific"/&gt;
    &lt;/timezone&gt;
&lt;/metazoneInfo&gt;


Use an XML to CSV converter. (I used https://www.convertcsv.com/xml-to-csv.htm)

Result:

usesMetazone/0/_mzone,_type,usesMetazone/0/_to, ...
GMT,Africa/Abidjan,,,,,,,,,,,,,,,,,,,,,,,,,
GMT,Africa/Accra,,,,,,,,,,,,,,,,,,,,,,,,,
...
Wallis,Pacific/Wallis,,,,,,,,,,,,,,,,,,,,,,,,,
America_Pacific,PST8PDT,,,,,,,,,,,,,,,,,,,,,,,,,


The first line describes the XML attributes extracted. The remaining lines start with "metazone,timezone".

The additional columns describe historical changes; e.g. "from date, to date, metazone". That is, some time zones used different metazones over specified date ranges.

Care must be taken when interpreting any timezone that has such historical data. The metazone in first column is probably NOT the CURRENT timezone; this is easier to see in the XML, where "metazone 0" is the first metazone mentioned, hence probably the EARLIEST one (it has a "to" date but no "from" date).



In that situation, I think the CURRENT metazone is always the LAST one mentioned (column farthest to right, that has a .../_mzone header).

For example, timezone "Africa/Algiers":

XML:

&lt;timezone type="Africa/Algiers"&gt;
    &lt;usesMetazone to="1977-10-20 23:00" mzone="Europe_Western"/&gt;
    &lt;usesMetazone to="1979-10-25 23:00" from="1977-10-20 23:00" mzone="Europe_Central"/&gt;
    &lt;usesMetazone to="1981-05-01 00:00" from="1979-10-25 23:00" mzone="Europe_Western"/&gt;
    &lt;usesMetazone from="1981-05-01 00:00" mzone="Europe_Central"/&gt;
&lt;/timezone&gt;


CSV:

Europe_Western,Africa/Algiers,1977-10-20 23:00,1979-10-25 23:00,1977-10-20 23:00,Europe_Central,1981-05-01 00:00,1979-10-25 23:00,Europe_Western,1981-05-01 00:00,Europe_Central,,,,,,,,,,,,,,,,


In Spreadsheet, we see that rightmost two columns are:

usesMetazone/3/_from   usesMetazone/3/_mzone
1981-05-01 00:00       Europe_Central


So current metazone is Europe_Central, NOT Europe_Western from first column.



Except for this issue getting the correct "current" metazone, the first two columns can be easily imported to DB: stackoverflow thead.