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: jerry@stripped
Web site: www.the-infoshop.com
>-----Original Message-----
>From: Daevid Vincent [mailto:daevid@stripped]
>Sent: Wednesday, October 13, 2010 10:51 PM
>To: mysql@stripped
>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=1
