Download Database

Time Zone database is updated randomly depends on changes. You may subscribe to our RSS or follow us at twitter for latest database updates. If you created script to download updates automatically, you can check the latest version date (UTC) at here.

The database is available in CSV or SQL format. Download the format which fullfill your needs.

File Name Size  
timezonedb.csv.zip (Updated on: 31 Aug, 2014) 148.99 kB
Download CSV format
timezonedb.sql.zip (Updated on: 31 Aug, 2014) 148.62 kB
Download SQL format

 

CSV Format

CSV package contains country.csv, timezone.csv, and zone.csv. The data is comma-delimited text in utf8 encoding.

country.csv
Fields: "country_code","country_name"

"TV","Tuvalu"
"TW","Taiwan"
"TZ","Tanzania"
"UA","Ukraine"
"UG","Uganda"
"UM","United States Minor Outlying Islands"
"US","United States"
"UY","Uruguay"
"UZ","Uzbekistan"
"VA","Vatican"

timezone.csv
Fields: "zone_id","abbreviation","time_start","gmt_offset","dst"

"370","CDT","-273686400","-18000","1"
"370","EST","-266432400","-18000","0"
"370","EDT","-52938000","-14400","1"
"370","EST","-37216800","-18000","0"
"370","EDT","-21488400","-14400","1"
"370","EST","-5767200","-18000","0"
"370","EDT","9961200","-14400","1"
"370","EST","25682400","-18000","0"
"370","EDT","41410800","-14400","1"
"370","EST","57736800","-18000","0"

zone.csv
Fields: "zone_id","country_code","zone_name"

"388","US","America/Phoenix"
"389","US","America/Los_Angeles"
"390","US","America/Anchorage"
"391","US","America/Juneau"
"392","US","America/Sitka"
"393","US","America/Yakutat"
"394","US","America/Nome"
"395","US","America/Adak"
"396","US","America/Metlakatla"
"397","US","Pacific/Honolulu"

 

Installation

These CSV data can easily load into your MySQL database. Use the following MySQL queries:

DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
	`country_code` CHAR(2) NULL,
	`country_name` VARCHAR(45) NULL,
	INDEX `idx_country_code` (`country_code`)
) COLLATE='utf8_bin' ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'country.csv' INTO TABLE `country` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';


DROP TABLE IF EXISTS `timezone`;
CREATE TABLE `timezone` (
	`zone_id` INT(10) NOT NULL,
	`abbreviation` VARCHAR(6) NOT NULL,
	`time_start` INT NOT NULL,
	`gmt_offset` INT NOT NULL,
	`dst` CHAR(1) NOT NULL,
	INDEX `idx_zone_id` (`zone_id`),
	INDEX `idx_time_start` (`time_start`)
) COLLATE='utf8_bin' ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'timezone.csv' INTO TABLE `timezone` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';


DROP TABLE IF EXISTS `zone`;
CREATE TABLE `zone` (
	`zone_id` INT(10) NOT NULL AUTO_INCREMENT,
	`country_code` CHAR(2) NOT NULL,
	`zone_name` VARCHAR(35) NOT NULL,
	PRIMARY KEY (`zone_id`),
	INDEX `idx_zone_name` (`zone_name`)
) COLLATE='utf8_bin' ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'zone.csv' INTO TABLE `zone` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

 

SQL Format

The SQL package contains timezonedb.sql, a SQL queries file dumped out by mysqldump. The data can directly load into your MySQL server using following command:

mysql -u <username> -p<password> timezonedb < timezonedb.sql

 

Usage

The validity of a time zone is depends on time_start field in the database. This is important to get the correct GMT offset especially areas having Daylight Saving Time. The example below showing how to query the time zone information using zone name America/Los_Angeles.

SELECT z.country_code, z.zone_name, tz.abbreviation, tz.gmt_offset, tz.dst
FROM `timezone` tz JOIN `zone` z
ON tz.zone_id=z.zone_id
WHERE tz.time_start < UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND z.zone_name='America/Los_Angeles'
ORDER BY tz.time_start DESC LIMIT 1;

Output:
"country_code","zone_name","abbreviation","gmt_offset","dst"
"US","America/Los_Angeles","PST","-28800","0"

In order to show the local time of a zone, please use the following query:
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(UTC_TIMESTAMP()) + tz.gmt_offset, '%a, %d %b %Y, %H:%i:%s') AS local_time
FROM `timezone` tz JOIN `zone` z
ON tz.zone_id=z.zone_id
WHERE tz.time_start < UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND z.zone_name='America/Los_Angeles'
ORDER BY tz.time_start DESC LIMIT 1;

Output:
"local_time"
"Tue, 02 Sep 2014, 10:47:14"

 

Free timezone database of world countries.