List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:October 14 2010 4:25pm
Subject:RE: How do I use and JOIN the mysql.time_zone% tables?
View as plain text  
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




Thread
How do I use and JOIN the mysql.time_zone% tables?Daevid Vincent14 Oct
  • Re: How do I use and JOIN the mysql.time_zone% tables?Johan De Meersman14 Oct
  • RE: How do I use and JOIN the mysql.time_zone% tables?Jerry Schwartz14 Oct