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 |
06 Sep, 2024 |
851.57 kB |
|
TimeZoneDB.sql.zip |
06 Sep, 2024 |
860.72 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;
Output:
"local_time"
"Sun, 03 Nov 2024, 16:44:27"