List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 14 2010 2:50am
Subject:How do I use and JOIN the mysql.time_zone% tables?
View as plain text  
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!

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