Skip navigation links

User Comments

Posted by Wes Dean on August 14 2010 5:22pm[Delete] [Edit]

I use a shared hosting service so the tables for mysql_tzinfo_to_sql are not available in my database. I went through a lot of trouble to get the structure for the tables, so I thought I would share.

CREATE TABLE `time_zone` (
`Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1676 DEFAULT CHARSET=utf8 COMMENT='Time zones';
CREATE TABLE `time_zone_leap_second` (
`Transition_time` bigint(20) NOT NULL,
`Correction` int(11) NOT NULL,
PRIMARY KEY (`Transition_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Leap seconds information for time zones';
CREATE TABLE `time_zone_name` (
`Name` char(64) NOT NULL,
`Time_zone_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names';
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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transitions';
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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transition types';

Posted by Scott Carter on August 8 2013 1:06pm[Delete] [Edit]

Hey Wes. Thanks for the table creation script. It worked great for me. Can you script out the data rows as well?

Posted by Mark Stafford on October 17 2013 7:17am[Delete] [Edit]

The first two links go over why you might want to generate the data for yourself. Failing that, the third provides two options that include the records.

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html

http://dev.mysql.com/downloads/timezones.html