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=arch...@jab.org
