Skip to content

Instantly share code, notes, and snippets.

@rpavlovic
Created February 15, 2015 13:59
Show Gist options
  • Save rpavlovic/31d713ab401ba8315c16 to your computer and use it in GitHub Desktop.
Save rpavlovic/31d713ab401ba8315c16 to your computer and use it in GitHub Desktop.
Create Time Zone Tables
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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment