Interesting question -- I hope someone can give an in-depth explanation.
I've created some TZ processing to use in Access, to go back and forth from
local time to UTC (which Access doesn't make easy). To do this, I had to find
and reformat some official files so I could cram them into Access tables. I
learned a lot doing that, and I'm trying to relate this all to the TZ tables
in MySQL.
The tables I wound up with were:
ISO3166_countries:
Country Code (the ISA standard abbreviation for the country, two alpha
characters)
Country Name (the ISO standard name of the country)
FIPS_regions:
Country Code (same as ISO3166 Country Code)
Region Code (identifies a part of a country, not unique across countries)
Time Zone ID
Time Zones:
Time Zone ID
Time Zone Name (ISO standard, I thought, but now I'm not so sure -- see
below)
Time Zones Data:
ID (record identifier), not part of the official data
Time Zone ID **multiple records per**
Time Zone Start (seconds before or after the start of the UNIX epoch)
GMT offset (seconds)
DST (true/false)
The reason that `Time Zones Data` has multiple records per `Time Zone ID` is
that the rules for a particular zone might (probably did) change throughout
history.
The best **rough** equivalence to the tables in MySQL seems to be
`Time Zones` - `mysql`.`time_zone_name`
`Time Zones Data` - `mysql`.`time_zone_transition`
but I don't know what `transition_type` means, and the time zone names don't
match what I thought were the ISO standards. For example, the data I found
when gathering my data has time zone 106 as America/New_York, whereas the
MySQL table `time_zone_name` has time zone 106 as America/Fort_Wayne.
Those two cities are not in the same time zone.
I have another question for the group: when are the time zone tables updated?
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Wednesday, October 13, 2010 10:51 PM
To: mysql@lists.mysql.com
Subject: How do I use and JOIN the mysql.time_zone% tables?
I'm trying to figure out how to join the mysql.time_zone% tables and make
sense of this.
YES, I know how to use them with SET time_zone = timezone; and all that.
http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
That is NOT what I need them for (yet).
I have a list of airports and those airports have cities and countries.
I need to correlate those cities (which may or may not have an exact match
in the time_zone_name table, so for each airport/city, I will need to hunt
down the right offset via some page like this:
http://www.timeanddate.com/worldclock/search.html
THEN store the Time_zone_id in my city table (or whatever the unique
combination is that I'd need and at this point I'm very confused as to what
that is).
The problem is I can find no real documentation what all these 5 tables are
each for (some are obvious, but so cryptic it's hard to digest them)
Moreover, I don't understand the results I'm getting...
SELECT * FROM time_zone_name WHERE `Name` LIKE '%brussels%';
Name Time_zone_id
-
Europe/Brussels 412
posix/Europe/Brussels 993
right/Europe/Brussels 1574
First, WTF are there THREE Brussels??
SELECT * FROM time_zone_transition_type WHERE Time_zone_id IN (412, 993,
1574);
Time_zone_id Transition_type_id Offset Is_DST Abbreviation
-- -- --
412 0 0 0 WET
412 13600 0 CET
412 27200 1 CEST
412 33600 0 CET
412 47200 1 CEST
412 53600 1 WEST
412 6 0 0 WET
412 7 0 0 WET
412 87200 1 CEST
412 93600 0 CET
993 0 0 0 WET
993 13600 0 CET
993 27200 1 CEST
993 33600 0 CET
993 47200 1 CEST
993 53600 1 WEST
993 6 0 0 WET
993 7 0 0 WET
993 87200 1 CEST
993 93600 0 CET
1574 0 0 0 WET
1574 13600 0 CET
1574 27200 1 CEST
1574 33600 0 CET
1574 4
