How do I use and JOIN the mysql.time

How do I use and JOIN the mysql.time_zone% tables?

am 14.10.2010 04:50:49 von Daevid Vincent

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.htm l
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=gcdmg-mysql-2@m.gmane.org

Re: How do I use and JOIN the mysql.time_zone% tables?

am 14.10.2010 08:23:38 von Johan De Meersman

--20cf301d3c222acdc304928dc0e8
Content-Type: text/plain; charset=ISO-8859-1

Part of your answer is the offset column, which seems to be relative to the
abbreviation used. This implies, to me, that each particular abbreviation
has it's own way of specifying the "starting point" of the time. Added is
the DST flag, which (probably) tells you that your app needs to keep
daylight savings time in mind.

I can't tell you why there are so many, or which one to pick, but there you
go. Just, you know, pick one, learn it's rules and stick to it.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--20cf301d3c222acdc304928dc0e8--

RE: How do I use and JOIN the mysql.time_zone% tables?

am 14.10.2010 18:25:30 von Jerry Schwartz

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@gii.co.jp
Web site: www.the-infoshop.com

>-----Original Message-----
>From: Daevid Vincent [mailto:daevid@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.ht ml
>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=jerry@gii.co.jp

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org