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 1 3600 0 CET > 412 2 7200 1 CEST > 412 3 3600 0 CET > 412 4 7200 1 CEST > 412 5 3600 1 WEST > 412 6 0 0 WET > 412 7 0 0 WET > 412 8 7200 1 CEST > 412 9 3600 0 CET > 993 0 0 0 WET > 993 1 3600 0 CET > 993 2 7200 1 CEST > 993 3 3600 0 CET > 993 4 7200 1 CEST > 993 5 3600 1 WEST > 993 6 0 0 WET > 993 7 0 0 WET > 993 8 7200 1 CEST > 993 9 3600 0 CET > 1574 0 0 0 WET > 1574 1 3600 0 CET > 1574 2 7200 1 CEST > 1574 3 3600 0 CET > 1574 4 7200 1 CEST > 1574 5 3600 1 WEST > 1574 6 0 0 WET > 1574 7 0 0 WET > 1574 8 7200 1 CEST > 1574 9 3600 0 CET > >Now WTF are there TEN rows PER? > >This page: http://www.timeanddate.com/worldclock/city.html?n=48 >says Brussels, Belgium is "CEST" so why are there 10 >time_zone_transition_types > >SELECT * >FROM time_zone_name > JOIN time_zone_transition_type > ON time_zone_name.Time_zone_id = > time_zone_transition_type.Time_zone_id >-- AND time_zone_transition.Transition_type_id = >-- time_zone_transition_type.Transition_type_id >WHERE `Name` LIKE '%brussels%'; > >Name Time_zone_id Transition_type_id Offset Is_DST >Abbreviation >--------------------- ------------ ------------------ ------ ------ >------------ >Europe/Brussels 412 0 0 0 >WET >Europe/Brussels 412 1 3600 0 >CET >Europe/Brussels 412 2 7200 1 >CEST >Europe/Brussels 412 3 3600 0 >CET >Europe/Brussels 412 4 7200 1 >CEST >Europe/Brussels 412 5 3600 1 >WEST >Europe/Brussels 412 6 0 0 >WET >Europe/Brussels 412 7 0 0 >WET >Europe/Brussels 412 8 7200 1 >CEST >Europe/Brussels 412 9 3600 0 >CET >posix/Europe/Brussels 993 0 0 0 >WET >posix/Europe/Brussels 993 1 3600 0 >CET >posix/Europe/Brussels 993 2 7200 1 >CEST >posix/Europe/Brussels 993 3 3600 0 >CET >posix/Europe/Brussels 993 4 7200 1 >CEST >posix/Europe/Brussels 993 5 3600 1 >WEST >posix/Europe/Brussels 993 6 0 0 >WET >posix/Europe/Brussels 993 7 0 0 >WET >posix/Europe/Brussels 993 8 7200 1 >CEST >posix/Europe/Brussels 993 9 3600 0 >CET >right/Europe/Brussels 1574 0 0 0 >WET >right/Europe/Brussels 1574 1 3600 0 >CET >right/Europe/Brussels 1574 2 7200 1 >CEST >right/Europe/Brussels 1574 3 3600 0 >CET >right/Europe/Brussels 1574 4 7200 1 >CEST >right/Europe/Brussels 1574 5 3600 1 >WEST >right/Europe/Brussels 1574 6 0 0 >WET >right/Europe/Brussels 1574 7 0 0 >WET >right/Europe/Brussels 1574 8 7200 1 >CEST >right/Europe/Brussels 1574 9 3600 0 >CET > >Why are there so many entries?? Which is the right one to use? > >And how does the "time_zone_transition" table fit into all this? > >It seems there's no way to join time_zone_transition AND >time_zone_transition_type AND time_zone_name because of the whacky way the >first two tables are declared... > >CREATE TABLE `time_zone_name` ( > `Name` char(64) NOT NULL, > `Time_zone_id` int(10) unsigned NOT NULL, > PRIMARY KEY (`Name`) >) > >CREATE TABLE `time_zone_transition_type` ( > `Time_zone_id` int(10) unsigned NOT NULL, > `Transition_type_id` int(10) unsigned NOT NULL, > `Offset` int(11) NOT NULL default '0', > `Is_DST` tinyint(3) unsigned NOT NULL default '0', > `Abbreviation` char(8) NOT NULL default '', > PRIMARY KEY (`Time_zone_id`,`Transition_type_id`) >) > >CREATE TABLE `time_zone_transition` ( > `Time_zone_id` int(10) unsigned NOT NULL, > `Transition_time` bigint(20) NOT NULL, > `Transition_type_id` int(10) unsigned NOT NULL, > PRIMARY KEY (`Time_zone_id`,`Transition_time`) >) > >If I try this: > >SELECT * >FROM time_zone_name > JOIN time_zone_transition_type > ON time_zone_name.Time_zone_id = > time_zone_transition_type.Time_zone_id > JOIN time_zone_transition > ON time_zone_transition.Time_zone_id = > time_zone_name.Time_zone_id > AND time_zone_transition.Transition_type_id = > time_zone_transition_type.Transition_type_id >WHERE `Name` LIKE '%brussels%'; > >I get 555 rows! > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
