Download Time Zone Database

Download Database

Time Zone database is updated randomly depends on changes. You may 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 fulfill your needs.

Starting 2022, all offline database releases will no longer include zone_id. This field will be deprecated and the time zone will be identified by their zone name.

File Name	Date	Size
TimeZoneDB.csv.zip	04 Nov, 2024	851.73 kB
TimeZoneDB.sql.zip	04 Nov, 2024	860.69 kB

CSV Format

CSV package contains country.csv and timezone.csv. The data is comma-delimited text in UTF-8 encoding.

country.csv
Fields: country_code,country_name

UG,Uganda
UA,Ukraine
AE,United Arab Emirates
GB,United Kingdom
US,United States
UM,United States Minor Outlying Islands
UY,Uruguay
UZ,Uzbekistan
VU,Vanuatu
VE,"Venezuela, Bolivarian Republic of"
VN,Viet Nam

time_zone.csv
Fields: zone_name,country_code,abbreviation,time_start,gmt_offset,dst

America/Los_Angeles,US,PDT,16195024800,-25200,1
America/Los_Angeles,US,PST,16215584400,-28800,0
America/Los_Angeles,US,PDT,16226474400,-25200,1
America/Los_Angeles,US,PST,16247034000,-28800,0
America/Los_Angeles,US,PDT,16257924000,-25200,1
America/Los_Angeles,US,PST,16278483600,-28800,0
America/Los_Angeles,US,PDT,16289373600,-25200,1
America/Los_Angeles,US,PST,16309933200,-28800,0
America/Los_Angeles,US,PDT,16320823200,-25200,1
America/Los_Angeles,US,PST,16341382800,-28800,0
America/Los_Angeles,US,PDT,16352877600,-25200,1
America/Los_Angeles,US,PST,16373437200,-28800,0
America/Los_Angeles,US,PDT,16384327200,-25200,1
America/Los_Angeles,US,PST,16404886800,-28800,0
America/Los_Angeles,US,PDT,16415776800,-25200,1

Installation

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

DROP TABLE IF EXISTS `time_zone`;
CREATE TABLE `time_zone` (
	`zone_name` VARCHAR(35) NOT NULL,
	`country_code` CHAR(2) 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_name` (`zone_name`),
	INDEX `idx_time_start` (`time_start`)
) COLLATE='utf8_bin' ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'time_zone.csv' INTO TABLE `time_zone` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

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';

SQL Format

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

mysql -u USERNAME -pPASSWORD timezonedb < time_zone.sql

Usage

The validity of a time zone 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 `country_code`, `zone_name`, `abbreviation`, `gmt_offset`, `dst`
FROM `time_zone`
WHERE `time_start` <= UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND `zone_name` = 'America/Los_Angeles'
ORDER BY `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()) + `gmt_offset`, '%a, %d %b %Y, %H:%i:%s') AS `local_time`
FROM `time_zone`
WHERE `time_start` <= UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND `zone_name` = 'America/Los_Angeles'
ORDER BY `time_start` DESC LIMIT 1;