Get timezone from GPS

If we need to manage time-sensitive data coming from distant areas in different timezone, then we need a method to convert the local timestamp into a unified timezone. I selected coordinated universal time, or in short UTC. What we need is a database to retrieve the timezone from the given location information.This section develops the data table and the stored procedure to meet the above demand. First, create the the time_zone table and populate it using the attached File:Time zone.csv. And then create the below stored procedure. The usage is straight-forward from the source.
DELIMITER ;;
CREATE FUNCTION `getTimeZone`(
        fLatitude DOUBLE,
        fLongitude DOUBLE
    ) RETURNS varchar(255)
    DETERMINISTIC
BEGIN

DECLARE v_foundTimeZone VARCHAR(255);
SET v_foundTimeZone = "";

SELECT
	tz.zonename INTO v_foundTimeZone
FROM
(
	SELECT
    	z.ISO3166_CountryCode,
    	z.zonename,
    	ABS(fLatitude - z.latitude)/90 + ABS(fLongitude - z.longitude)/180 AS diff
	FROM time_zone AS z
	ORDER BY diff ASC
	LIMIT 1
) AS tz;

RETURN v_foundTimeZone;

END;;
DELIMITER ;
Usage example:
DROP PROCEDURE IF EXISTS `getTimeZoneAtTime`;
DELIMITER ;;
CREATE PROCEDURE `getTimeZoneAtTime`(IN searchTime VARCHAR(255))
BEGIN

DECLARE foundLatitude VARCHAR(255);
DECLARE foundLongitude VARCHAR(255);
DECLARE foundTimestamp TIMESTAMP;
DECLARE foundUnixtime INT;
DECLARE selectedTimeZone VARCHAR(255);

SELECT 
	key_value, eml_event_timestamp, unixtimestamp INTO foundLatitude, foundTimestamp, foundUnixtime
FROM elog_user_1_sensor 
WHERE   
sensor LIKE '%GPSLocationEvent' AND
sensor_key = 'latitude' AND
(eml_event_timestamp BETWEEN (searchTime - INTERVAL 1 DAY) AND (searchTime + INTERVAL 1 DAY))
ORDER BY (unixtimestamp - UNIX_TIMESTAMP(searchTime)) ASC
LIMIT 1;

SELECT 
	key_value, eml_event_timestamp, unixtimestamp INTO foundLongitude, foundTimestamp, foundUnixtime
FROM elog_user_1_sensor 
WHERE   
sensor LIKE '%GPSLocationEvent' AND
sensor_key = 'longitude' AND
(eml_event_timestamp BETWEEN (searchTime - INTERVAL 1 DAY) AND (searchTime + INTERVAL 1 DAY))
ORDER BY (unixtimestamp - UNIX_TIMESTAMP(searchTime)) ASC
LIMIT 1;

IF (foundLatitude IS NULL OR foundLongitude IS NULL) THEN
	SELECT '' AS selectedTimeZone, FROM_UNIXTIME(userUnixTimestamp) AS userTimestamp; 
	SELECT 
	searchTime,
	'' AS selectedTimeZone,
	searchTime AS eml_event_timestamp,
	UNIX_TIMESTAMP(searchTime) AS unixtime;
ELSE
	SET selectedTimeZone = getTimeZone(CAST(foundLatitude AS DECIMAL(3.10)), CAST(foundLongitude AS DECIMAL(3.10)));
	SET time_zone = selectedTimeZone;
	
	SELECT 
	searchTime,
	selectedTimeZone,
	foundTimestamp AS eml_event_timestamp,
	foundUnixtime AS unixtime;
END IF;
	
END;;
DELIMITER ;
Prepare the stored procedure above and call it
CALL getTimeZoneAtTime('2010-11-03');

Return results may look like

searchTime	selectedTimeZone	eml_event_timestamp	unixtime
2010-11-03	Europe/Ljubljana	2010-11-03 10:39:25	1288777165
One more MySQL stored procedure example:
DROP PROCEDURE IF EXISTS `getUserLocalTimeInformation`;
DELIMITER ;;
CREATE PROCEDURE `getUserLocalTimeInformation`(IN userUnixTimestamp INT)
BEGIN

DECLARE foundLatitude VARCHAR(255);
DECLARE foundLongitude VARCHAR(255);
DECLARE foundTimestamp TIMESTAMP;
DECLARE foundUnixtime INT;
DECLARE selectedTimeZone VARCHAR(255);
DECLARE userTimestamp TIMESTAMP;

-- This may vary over 27 hours. So we choose 2 day interval
SELECT FROM_UNIXTIME(userUnixTimestamp) INTO userTimestamp;

SELECT 
	key_value, eml_event_timestamp, unixtimestamp INTO foundLatitude, foundTimestamp, foundUnixtime
FROM elog_user_1_sensor 
WHERE   
sensor LIKE '%GPSLocationEvent' AND
sensor_key = 'latitude' AND
(eml_event_timestamp BETWEEN (userTimestamp - INTERVAL 1 DAY) AND (userTimestamp + INTERVAL 1 DAY))
ORDER BY (unixtimestamp - userUnixTimestamp) ASC
LIMIT 1;

SELECT 
	key_value, eml_event_timestamp, unixtimestamp INTO foundLongitude, foundTimestamp, foundUnixtime
FROM elog_user_1_sensor 
WHERE   
sensor LIKE '%GPSLocationEvent' AND
sensor_key = 'longitude' AND
(eml_event_timestamp BETWEEN (userTimestamp - INTERVAL 1 DAY) AND (userTimestamp + INTERVAL 1 DAY))
ORDER BY (unixtimestamp - userUnixTimestamp) ASC
LIMIT 1;

IF (foundLatitude IS NULL OR foundLongitude IS NULL) THEN
	SELECT '' AS selectedTimeZone, FROM_UNIXTIME(userUnixTimestamp) AS userTimestamp; 
ELSE
	SET selectedTimeZone = getTimeZone(CAST(foundLatitude AS DECIMAL(3.10)), CAST(foundLongitude AS DECIMAL(3.10)));
	SET time_zone = selectedTimeZone;
	
	SELECT selectedTimeZone, FROM_UNIXTIME(userUnixTimestamp) AS userTimestamp;
END IF;

END;;
DELIMITER ;
Another example in SQL function:
DROP FUNCTION IF EXISTS `getUserLocalTime`;
DELIMITER ;;
CREATE FUNCTION `getUserLocalTime`(
       userUnixTimestamp INT
    ) RETURNS TIMESTAMP
     
BEGIN
 
DECLARE foundLatitude VARCHAR(255);
DECLARE foundLongitude VARCHAR(255);
DECLARE foundTimestamp TIMESTAMP;
DECLARE foundUnixtime INT;
DECLARE selectedTimeZone VARCHAR(255);
DECLARE userTimestamp TIMESTAMP;
 
-- This may vary over 27 hours. So we choose 2 day interval
SELECT FROM_UNIXTIME(userUnixTimestamp) INTO userTimestamp;
 
SELECT
    key_value, eml_event_timestamp, unixtimestamp INTO foundLatitude, foundTimestamp, foundUnixtime
FROM elog_user_1_sensor 
WHERE  
sensor LIKE '%GPSLocationEvent' AND
sensor_key = 'latitude' AND
(eml_event_timestamp BETWEEN (userTimestamp - INTERVAL 1 DAY) AND (userTimestamp + INTERVAL 1 DAY))
ORDER BY (unixtimestamp - userUnixTimestamp) ASC
LIMIT 1;
 
SELECT
    key_value, eml_event_timestamp, unixtimestamp INTO foundLongitude, foundTimestamp, foundUnixtime
FROM elog_user_1_sensor 
WHERE  
sensor LIKE '%GPSLocationEvent' AND
sensor_key = 'longitude' AND
(eml_event_timestamp BETWEEN (userTimestamp - INTERVAL 1 DAY) AND (userTimestamp + INTERVAL 1 DAY))
ORDER BY (unixtimestamp - userUnixTimestamp) ASC
LIMIT 1;
 

IF (foundLatitude IS NULL OR foundLongitude IS NULL) THEN
	RETURN FROM_UNIXTIME(userUnixTimestamp);
ELSE
	SET selectedTimeZone = getTimeZone(CAST(foundLatitude AS DECIMAL(3.10)), CAST(foundLongitude AS DECIMAL(3.10)));
	SET time_zone = selectedTimeZone;
	RETURN FROM_UNIXTIME(userUnixTimestamp);
END IF;

END;;
DELIMITER ;
Prepare time_zone table using the below SQL code:

CREATE TABLE `time_zone` (
  `ISO3166_CountryCode` char(2) DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `zonename` varchar(128) DEFAULT NULL,
  `zonedescription` varchar(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `time_zone` WRITE;
/*!40000 ALTER TABLE `time_zone` DISABLE KEYS */;

INSERT INTO `time_zone` (`ISO3166_CountryCode`, `latitude`, `longitude`, `zonename`, `zonedescription`)
VALUES
	('AD',42.3,1.31,'Europe/Andorra',''),
	('AE',25.18,55.18,'Asia/Dubai',''),
	('AF',34.31,69.12,'Asia/Kabul',''),
	('AG',17.03,-61.48,'America/Antigua',''),
	('AI',18.12,-63.04,'America/Anguilla',''),
	('AL',41.2,19.5,'Europe/Tirane',''),
	('AM',40.11,44.3,'Asia/Yerevan',''),
	('AN',12.11,-69,'America/Curacao',''),
	('AO',-8.48,13.14,'Africa/Luanda',''),
	('AQ',-77.5,166.36,'Antarctica/McMurdo','McMurdo Station, Ross Island'),
	('AQ',-90,0,'Antarctica/South_Pole','Amundsen-Scott Station, South Pole'),
	('AQ',-67.34,-68.08,'Antarctica/Rothera','Rothera Station, Adelaide Island'),
	('AQ',-64.48,-64.06,'Antarctica/Palmer','Palmer Station, Anvers Island'),
	('AQ',-67.36,62.53,'Antarctica/Mawson','Mawson Station, Holme Bay'),
	('AQ',-68.35,77.58,'Antarctica/Davis','Davis Station, Vestfold Hills'),
	('AQ',-66.17,110.31,'Antarctica/Casey','Casey Station, Bailey Peninsula'),
	('AQ',-78.24,106.54,'Antarctica/Vostok','Vostok Station, S Magnetic Pole'),
	('AQ',-66.4,140.01,'Antarctica/DumontDUrville','Dumont-d\'Urville Station, Terre Adelie'),
	('AQ',-69.0022,39.3524,'Antarctica/Syowa','Syowa Station, E Ongul I'),
	('AQ',-54.3,158.57,'Antarctica/Macquarie','Macquarie Island Station, Macquarie Island'),
	('AR',-34.36,-58.27,'America/Argentina/Buenos_Aires','Buenos Aires (BA, CF)'),
	('AR',-31.24,-64.11,'America/Argentina/Cordoba','most locations (CB, CC, CN, ER, FM, MN, SE, SF)'),
	('AR',-24.47,-65.25,'America/Argentina/Salta','(SA, LP, NQ, RN)'),
	('AR',-24.11,-65.18,'America/Argentina/Jujuy','Jujuy (JY)'),
	('AR',-26.49,-65.13,'America/Argentina/Tucuman','Tucuman (TM)'),
	('AR',-28.28,-65.47,'America/Argentina/Catamarca','Catamarca (CT), Chubut (CH)'),
	('AR',-29.26,-66.51,'America/Argentina/La_Rioja','La Rioja (LR)'),
	('AR',-31.32,-68.31,'America/Argentina/San_Juan','San Juan (SJ)'),
	('AR',-32.53,-68.49,'America/Argentina/Mendoza','Mendoza (MZ)'),
	('AR',-33.19,-66.21,'America/Argentina/San_Luis','San Luis (SL)'),
	('AR',-51.38,-69.13,'America/Argentina/Rio_Gallegos','Santa Cruz (SC)'),
	('AR',-54.48,-68.18,'America/Argentina/Ushuaia','Tierra del Fuego (TF)'),
	('AS',-14.16,-170.42,'Pacific/Pago_Pago',''),
	('AT',48.13,16.2,'Europe/Vienna',''),
	('AU',-31.33,159.05,'Australia/Lord_Howe','Lord Howe Island'),
	('AU',-42.53,147.19,'Australia/Hobart','Tasmania - most locations'),
	('AU',-39.56,143.52,'Australia/Currie','Tasmania - King Island'),
	('AU',-37.49,144.58,'Australia/Melbourne','Victoria'),
	('AU',-33.52,151.13,'Australia/Sydney','New South Wales - most locations'),
	('AU',-31.57,141.27,'Australia/Broken_Hill','New South Wales - Yancowinna'),
	('AU',-27.28,153.02,'Australia/Brisbane','Queensland - most locations'),
	('AU',-20.16,149,'Australia/Lindeman','Queensland - Holiday Islands'),
	('AU',-34.55,138.35,'Australia/Adelaide','South Australia'),
	('AU',-12.28,130.5,'Australia/Darwin','Northern Territory'),
	('AU',-31.57,115.51,'Australia/Perth','Western Australia - most locations'),
	('AU',-31.43,128.52,'Australia/Eucla','Western Australia - Eucla area'),
	('AW',12.3,-69.58,'America/Aruba',''),
	('AX',60.06,19.57,'Europe/Mariehamn',''),
	('AZ',40.23,49.51,'Asia/Baku',''),
	('BA',43.52,18.25,'Europe/Sarajevo',''),
	('BB',13.06,-59.37,'America/Barbados',''),
	('BD',23.43,90.25,'Asia/Dhaka',''),
	('BE',50.5,4.2,'Europe/Brussels',''),
	('BF',12.22,-1.31,'Africa/Ouagadougou',''),
	('BG',42.41,23.19,'Europe/Sofia',''),
	('BH',26.23,50.35,'Asia/Bahrain',''),
	('BI',-3.23,29.22,'Africa/Bujumbura',''),
	('BJ',6.29,2.37,'Africa/Porto-Novo',''),
	('BL',17.53,-62.51,'America/St_Barthelemy',''),
	('BM',32.17,-64.46,'Atlantic/Bermuda',''),
	('BN',4.56,114.55,'Asia/Brunei',''),
	('BO',-16.3,-68.09,'America/La_Paz',''),
	('BR',-3.51,-32.25,'America/Noronha','Atlantic islands'),
	('BR',-1.27,-48.29,'America/Belem','Amapa, E Para'),
	('BR',-3.43,-38.3,'America/Fortaleza','NE Brazil (MA, PI, CE, RN, PB)'),
	('BR',-8.03,-34.54,'America/Recife','Pernambuco'),
	('BR',-7.12,-48.12,'America/Araguaina','Tocantins'),
	('BR',-9.4,-35.43,'America/Maceio','Alagoas, Sergipe'),
	('BR',-12.59,-38.31,'America/Bahia','Bahia'),
	('BR',-23.32,-46.37,'America/Sao_Paulo','S & SE Brazil (GO, DF, MG, ES, RJ, SP, PR, SC, RS)'),
	('BR',-20.27,-54.37,'America/Campo_Grande','Mato Grosso do Sul'),
	('BR',-15.35,-56.05,'America/Cuiaba','Mato Grosso'),
	('BR',-2.26,-54.52,'America/Santarem','W Para'),
	('BR',-8.46,-63.54,'America/Porto_Velho','Rondonia'),
	('BR',2.49,-60.4,'America/Boa_Vista','Roraima'),
	('BR',-3.08,-60.01,'America/Manaus','E Amazonas'),
	('BR',-6.4,-69.52,'America/Eirunepe','W Amazonas'),
	('BR',-9.58,-67.48,'America/Rio_Branco','Acre'),
	('BS',25.05,-77.21,'America/Nassau',''),
	('BT',27.28,89.39,'Asia/Thimphu',''),
	('BW',-24.39,25.55,'Africa/Gaborone',''),
	('BY',53.54,27.34,'Europe/Minsk',''),
	('BZ',17.3,-88.12,'America/Belize',''),
	('CA',47.34,-52.43,'America/St_Johns','Newfoundland Time, including SE Labrador'),
	('CA',44.39,-63.36,'America/Halifax','Atlantic Time - Nova Scotia (most places), PEI'),
	('CA',46.12,-59.57,'America/Glace_Bay','Atlantic Time - Nova Scotia - places that did not observe DST 1966-1971'),
	('CA',46.06,-64.47,'America/Moncton','Atlantic Time - New Brunswick'),
	('CA',53.2,-60.25,'America/Goose_Bay','Atlantic Time - Labrador - most locations'),
	('CA',51.25,-57.07,'America/Blanc-Sablon','Atlantic Standard Time - Quebec - Lower North Shore'),
	('CA',45.31,-73.34,'America/Montreal','Eastern Time - Quebec - most locations'),
	('CA',43.39,-79.23,'America/Toronto','Eastern Time - Ontario - most locations'),
	('CA',49.01,-88.16,'America/Nipigon','Eastern Time - Ontario & Quebec - places that did not observe DST 1967-1973'),
	('CA',48.23,-89.15,'America/Thunder_Bay','Eastern Time - Thunder Bay, Ontario'),
	('CA',63.44,-68.28,'America/Iqaluit','Eastern Time - east Nunavut - most locations'),
	('CA',66.08,-65.44,'America/Pangnirtung','Eastern Time - Pangnirtung, Nunavut'),
	('CA',74.4144,-94.4945,'America/Resolute','Eastern Standard Time - Resolute, Nunavut'),
	('CA',48.4531,-91.3718,'America/Atikokan','Eastern Standard Time - Atikokan, Ontario and Southampton I, Nunavut'),
	('CA',62.49,-92.0459,'America/Rankin_Inlet','Central Time - central Nunavut'),
	('CA',49.53,-97.09,'America/Winnipeg','Central Time - Manitoba & west Ontario'),
	('CA',48.43,-94.34,'America/Rainy_River','Central Time - Rainy River & Fort Frances, Ontario'),
	('CA',50.24,-104.39,'America/Regina','Central Standard Time - Saskatchewan - most locations'),
	('CA',50.17,-107.5,'America/Swift_Current','Central Standard Time - Saskatchewan - midwest'),
	('CA',53.33,-113.28,'America/Edmonton','Mountain Time - Alberta, east British Columbia & west Saskatchewan'),
	('CA',69.065,-105.031,'America/Cambridge_Bay','Mountain Time - west Nunavut'),
	('CA',62.27,-114.21,'America/Yellowknife','Mountain Time - central Northwest Territories'),
	('CA',68.2059,-133.43,'America/Inuvik','Mountain Time - west Northwest Territories'),
	('CA',59.46,-120.14,'America/Dawson_Creek','Mountain Standard Time - Dawson Creek & Fort Saint John, British Columbia'),
	('CA',49.16,-123.07,'America/Vancouver','Pacific Time - west British Columbia'),
	('CA',60.43,-135.03,'America/Whitehorse','Pacific Time - south Yukon'),
	('CA',64.04,-139.25,'America/Dawson','Pacific Time - north Yukon'),
	('CC',-12.1,96.55,'Indian/Cocos',''),
	('CD',-4.18,15.18,'Africa/Kinshasa','west Dem. Rep. of Congo'),
	('CD',-11.4,27.28,'Africa/Lubumbashi','east Dem. Rep. of Congo'),
	('CF',4.22,18.35,'Africa/Bangui',''),
	('CG',-4.16,15.17,'Africa/Brazzaville',''),
	('CH',47.23,8.32,'Europe/Zurich',''),
	('CI',5.19,-4.02,'Africa/Abidjan',''),
	('CK',-21.14,-159.46,'Pacific/Rarotonga',''),
	('CL',-33.27,-70.4,'America/Santiago','most locations'),
	('CL',-27.09,-109.26,'Pacific/Easter','Easter Island & Sala y Gomez'),
	('CM',4.03,9.42,'Africa/Douala',''),
	('CN',31.14,121.28,'Asia/Shanghai','east China - Beijing, Guangdong, Shanghai, etc.'),
	('CN',45.45,126.41,'Asia/Harbin','Heilongjiang (except Mohe), Jilin'),
	('CN',29.34,106.35,'Asia/Chongqing','central China - Sichuan, Yunnan, Guangxi, Shaanxi, Guizhou, etc.'),
	('CN',43.48,87.35,'Asia/Urumqi','most of Tibet & Xinjiang'),
	('CN',39.29,75.59,'Asia/Kashgar','west Tibet & Xinjiang'),
	('CO',4.36,-74.05,'America/Bogota',''),
	('CR',9.56,-84.05,'America/Costa_Rica',''),
	('CU',23.08,-82.22,'America/Havana',''),
	('CV',14.55,-23.31,'Atlantic/Cape_Verde',''),
	('CX',-10.25,105.43,'Indian/Christmas',''),
	('CY',35.1,33.22,'Asia/Nicosia',''),
	('CZ',50.05,14.26,'Europe/Prague',''),
	('DE',52.3,13.22,'Europe/Berlin',''),
	('DJ',11.36,43.09,'Africa/Djibouti',''),
	('DK',55.4,12.35,'Europe/Copenhagen',''),
	('DM',15.18,-61.24,'America/Dominica',''),
	('DO',18.28,-69.54,'America/Santo_Domingo',''),
	('DZ',36.47,3.03,'Africa/Algiers',''),
	('EC',-2.1,-79.5,'America/Guayaquil','mainland'),
	('EC',-0.54,-89.36,'Pacific/Galapagos','Galapagos Islands'),
	('EE',59.25,24.45,'Europe/Tallinn',''),
	('EG',30.03,31.15,'Africa/Cairo',''),
	('EH',27.09,-13.12,'Africa/El_Aaiun',''),
	('ER',15.2,38.53,'Africa/Asmara',''),
	('ES',40.24,-3.41,'Europe/Madrid','mainland'),
	('ES',35.53,-5.19,'Africa/Ceuta','Ceuta & Melilla'),
	('ES',28.06,-15.24,'Atlantic/Canary','Canary Islands'),
	('ET',9.02,38.42,'Africa/Addis_Ababa',''),
	('FI',60.1,24.58,'Europe/Helsinki',''),
	('FJ',-18.08,178.25,'Pacific/Fiji',''),
	('FK',-51.42,-57.51,'Atlantic/Stanley',''),
	('FM',7.25,151.47,'Pacific/Truk','Truk (Chuuk) and Yap'),
	('FM',6.58,158.13,'Pacific/Ponape','Ponape (Pohnpei)'),
	('FM',5.19,162.59,'Pacific/Kosrae','Kosrae'),
	('FO',62.01,-6.46,'Atlantic/Faroe',''),
	('FR',48.52,2.2,'Europe/Paris',''),
	('GA',0.23,9.27,'Africa/Libreville',''),
	('GB',51.303,-0.0731,'Europe/London',''),
	('GD',12.03,-61.45,'America/Grenada',''),
	('GE',41.43,44.49,'Asia/Tbilisi',''),
	('GF',4.56,-52.2,'America/Cayenne',''),
	('GG',49.27,-2.32,'Europe/Guernsey',''),
	('GH',5.33,-0.13,'Africa/Accra',''),
	('GI',36.08,-5.21,'Europe/Gibraltar',''),
	('GL',64.11,-51.44,'America/Godthab','most locations'),
	('GL',76.46,-18.4,'America/Danmarkshavn','east coast, north of Scoresbysund'),
	('GL',70.29,-21.58,'America/Scoresbysund','Scoresbysund / Ittoqqortoormiit'),
	('GL',76.34,-68.47,'America/Thule','Thule / Pituffik'),
	('GM',13.28,-16.39,'Africa/Banjul',''),
	('GN',9.31,-13.43,'Africa/Conakry',''),
	('GP',16.14,-61.32,'America/Guadeloupe',''),
	('GQ',3.45,8.47,'Africa/Malabo',''),
	('GR',37.58,23.43,'Europe/Athens',''),
	('GS',-54.16,-36.32,'Atlantic/South_Georgia',''),
	('GT',14.38,-90.31,'America/Guatemala',''),
	('GU',13.28,144.45,'Pacific/Guam',''),
	('GW',11.51,-15.35,'Africa/Bissau',''),
	('GY',6.48,-58.1,'America/Guyana',''),
	('HK',22.17,114.09,'Asia/Hong_Kong',''),
	('HN',14.06,-87.13,'America/Tegucigalpa',''),
	('HR',45.48,15.58,'Europe/Zagreb',''),
	('HT',18.32,-72.2,'America/Port-au-Prince',''),
	('HU',47.3,19.05,'Europe/Budapest',''),
	('ID',-6.1,106.48,'Asia/Jakarta','Java & Sumatra'),
	('ID',-0.02,109.2,'Asia/Pontianak','west & central Borneo'),
	('ID',-5.07,119.24,'Asia/Makassar','east & south Borneo, Celebes, Bali, Nusa Tengarra, west Timor'),
	('ID',-2.32,140.42,'Asia/Jayapura','Irian Jaya & the Moluccas'),
	('IE',53.2,-6.15,'Europe/Dublin',''),
	('IL',31.46,35.14,'Asia/Jerusalem',''),
	('IM',54.09,-4.28,'Europe/Isle_of_Man',''),
	('IN',22.32,88.22,'Asia/Kolkata',''),
	('IO',-7.2,72.25,'Indian/Chagos',''),
	('IQ',33.21,44.25,'Asia/Baghdad',''),
	('IR',35.4,51.26,'Asia/Tehran',''),
	('IS',64.09,-21.51,'Atlantic/Reykjavik',''),
	('IT',41.54,12.29,'Europe/Rome',''),
	('JE',49.12,-2.07,'Europe/Jersey',''),
	('JM',18,-76.48,'America/Jamaica',''),
	('JO',31.57,35.56,'Asia/Amman',''),
	('JP',35.3916,139.444,'Asia/Tokyo',''),
	('KE',-1.17,36.49,'Africa/Nairobi',''),
	('KG',42.54,74.36,'Asia/Bishkek',''),
	('KH',11.33,104.55,'Asia/Phnom_Penh',''),
	('KI',1.25,173,'Pacific/Tarawa','Gilbert Islands'),
	('KI',-3.08,-171.05,'Pacific/Enderbury','Phoenix Islands'),
	('KI',1.52,-157.2,'Pacific/Kiritimati','Line Islands'),
	('KM',-11.41,43.16,'Indian/Comoro',''),
	('KN',17.18,-62.43,'America/St_Kitts',''),
	('KP',39.01,125.45,'Asia/Pyongyang',''),
	('KR',37.33,126.58,'Asia/Seoul',''),
	('KW',29.2,47.59,'Asia/Kuwait',''),
	('KY',19.18,-81.23,'America/Cayman',''),
	('KZ',43.15,76.57,'Asia/Almaty','most locations'),
	('KZ',44.48,65.28,'Asia/Qyzylorda','Qyzylorda (Kyzylorda, Kzyl-Orda)'),
	('KZ',50.17,57.1,'Asia/Aqtobe','Aqtobe (Aktobe)'),
	('KZ',44.31,50.16,'Asia/Aqtau','Atyrau (Atirau, Gur\'yev), Mangghystau (Mankistau)'),
	('KZ',51.13,51.21,'Asia/Oral','West Kazakhstan'),
	('LA',17.58,102.36,'Asia/Vientiane',''),
	('LB',33.53,35.3,'Asia/Beirut',''),
	('LC',14.01,-61,'America/St_Lucia',''),
	('LI',47.09,9.31,'Europe/Vaduz',''),
	('LK',6.56,79.51,'Asia/Colombo',''),
	('LR',6.18,-10.47,'Africa/Monrovia',''),
	('LS',-29.28,27.3,'Africa/Maseru',''),
	('LT',54.41,25.19,'Europe/Vilnius',''),
	('LU',49.36,6.09,'Europe/Luxembourg',''),
	('LV',56.57,24.06,'Europe/Riga',''),
	('LY',32.54,13.11,'Africa/Tripoli',''),
	('MA',33.39,-7.35,'Africa/Casablanca',''),
	('MC',43.42,7.23,'Europe/Monaco',''),
	('MD',47,28.5,'Europe/Chisinau',''),
	('ME',42.26,19.16,'Europe/Podgorica',''),
	('MF',18.04,-63.05,'America/Marigot',''),
	('MG',-18.55,47.31,'Indian/Antananarivo',''),
	('MH',7.09,171.12,'Pacific/Majuro','most locations'),
	('MH',9.05,167.2,'Pacific/Kwajalein','Kwajalein'),
	('MK',41.59,21.26,'Europe/Skopje',''),
	('ML',12.39,-8,'Africa/Bamako',''),
	('MM',16.47,96.1,'Asia/Rangoon',''),
	('MN',47.55,106.53,'Asia/Ulaanbaatar','most locations'),
	('MN',48.01,91.39,'Asia/Hovd','Bayan-Olgiy, Govi-Altai, Hovd, Uvs, Zavkhan'),
	('MN',48.04,114.3,'Asia/Choibalsan','Dornod, Sukhbaatar'),
	('MO',22.14,113.35,'Asia/Macau',''),
	('MP',15.12,145.45,'Pacific/Saipan',''),
	('MQ',14.36,-61.05,'America/Martinique',''),
	('MR',18.06,-15.57,'Africa/Nouakchott',''),
	('MS',16.43,-62.13,'America/Montserrat',''),
	('MT',35.54,14.31,'Europe/Malta',''),
	('MU',-20.1,57.3,'Indian/Mauritius',''),
	('MV',4.1,73.3,'Indian/Maldives',''),
	('MW',-15.47,35,'Africa/Blantyre',''),
	('MX',19.24,-99.09,'America/Mexico_City','Central Time - most locations'),
	('MX',21.05,-86.46,'America/Cancun','Central Time - Quintana Roo'),
	('MX',20.58,-89.37,'America/Merida','Central Time - Campeche, Yucatan'),
	('MX',25.4,-100.19,'America/Monterrey','Mexican Central Time - Coahuila, Durango, Nuevo Leon, Tamaulipas away from US border'),
	('MX',25.5,-97.3,'America/Matamoros','US Central Time - Coahuila, Durango, Nuevo Leon, Tamaulipas near US border'),
	('MX',23.13,-106.25,'America/Mazatlan','Mountain Time - S Baja, Nayarit, Sinaloa'),
	('MX',28.38,-106.05,'America/Chihuahua','Mexican Mountain Time - Chihuahua away from US border'),
	('MX',29.34,-104.25,'America/Ojinaga','US Mountain Time - Chihuahua near US border'),
	('MX',29.04,-110.58,'America/Hermosillo','Mountain Standard Time - Sonora'),
	('MX',32.32,-117.01,'America/Tijuana','US Pacific Time - Baja California near US border'),
	('MX',30.18,-114.52,'America/Santa_Isabel','Mexican Pacific Time - Baja California away from US border'),
	('MY',3.1,101.42,'Asia/Kuala_Lumpur','peninsular Malaysia'),
	('MY',1.33,110.2,'Asia/Kuching','Sabah & Sarawak'),
	('MZ',-25.58,32.35,'Africa/Maputo',''),
	('NA',-22.34,17.06,'Africa/Windhoek',''),
	('NC',-22.16,166.27,'Pacific/Noumea',''),
	('NE',13.31,2.07,'Africa/Niamey',''),
	('NF',-29.03,167.58,'Pacific/Norfolk',''),
	('NG',6.27,3.24,'Africa/Lagos',''),
	('NI',12.09,-86.17,'America/Managua',''),
	('NL',52.22,4.54,'Europe/Amsterdam',''),
	('NO',59.55,10.45,'Europe/Oslo',''),
	('NP',27.43,85.19,'Asia/Kathmandu',''),
	('NR',-0.31,166.55,'Pacific/Nauru',''),
	('NU',-19.01,-169.55,'Pacific/Niue',''),
	('NZ',-36.52,174.46,'Pacific/Auckland','most locations'),
	('NZ',-43.57,-176.33,'Pacific/Chatham','Chatham Islands'),
	('OM',23.36,58.35,'Asia/Muscat',''),
	('PA',8.58,-79.32,'America/Panama',''),
	('PE',-12.03,-77.03,'America/Lima',''),
	('PF',-17.32,-149.34,'Pacific/Tahiti','Society Islands'),
	('PF',-9,-139.3,'Pacific/Marquesas','Marquesas Islands'),
	('PF',-23.08,-134.57,'Pacific/Gambier','Gambier Islands'),
	('PG',-9.3,147.1,'Pacific/Port_Moresby',''),
	('PH',14.35,121,'Asia/Manila',''),
	('PK',24.52,67.03,'Asia/Karachi',''),
	('PL',52.15,21,'Europe/Warsaw',''),
	('PM',47.03,-56.2,'America/Miquelon',''),
	('PN',-25.04,-130.05,'Pacific/Pitcairn',''),
	('PR',18.2806,-66.0622,'America/Puerto_Rico',''),
	('PS',31.3,34.28,'Asia/Gaza',''),
	('PT',38.43,-9.08,'Europe/Lisbon','mainland'),
	('PT',32.38,-16.54,'Atlantic/Madeira','Madeira Islands'),
	('PT',37.44,-25.4,'Atlantic/Azores','Azores'),
	('PW',7.2,134.29,'Pacific/Palau',''),
	('PY',-25.16,-57.4,'America/Asuncion',''),
	('QA',25.17,51.32,'Asia/Qatar',''),
	('RE',-20.52,55.28,'Indian/Reunion',''),
	('RO',44.26,26.06,'Europe/Bucharest',''),
	('RS',44.5,20.3,'Europe/Belgrade',''),
	('RU',54.43,20.3,'Europe/Kaliningrad','Moscow-01 - Kaliningrad'),
	('RU',55.45,37.35,'Europe/Moscow','Moscow+00 - west Russia'),
	('RU',48.44,44.25,'Europe/Volgograd','Moscow+00 - Caspian Sea'),
	('RU',53.12,50.09,'Europe/Samara','Moscow - Samara, Udmurtia'),
	('RU',56.51,60.36,'Asia/Yekaterinburg','Moscow+02 - Urals'),
	('RU',55,73.24,'Asia/Omsk','Moscow+03 - west Siberia'),
	('RU',55.02,82.55,'Asia/Novosibirsk','Moscow+03 - Novosibirsk'),
	('RU',53.45,87.07,'Asia/Novokuznetsk','Moscow+03 - Novokuznetsk'),
	('RU',56.01,92.5,'Asia/Krasnoyarsk','Moscow+04 - Yenisei River'),
	('RU',52.16,104.2,'Asia/Irkutsk','Moscow+05 - Lake Baikal'),
	('RU',62,129.4,'Asia/Yakutsk','Moscow+06 - Lena River'),
	('RU',43.1,131.56,'Asia/Vladivostok','Moscow+07 - Amur River'),
	('RU',46.58,142.42,'Asia/Sakhalin','Moscow+07 - Sakhalin Island'),
	('RU',59.34,150.48,'Asia/Magadan','Moscow+08 - Magadan'),
	('RU',53.01,158.39,'Asia/Kamchatka','Moscow+08 - Kamchatka'),
	('RU',64.45,177.29,'Asia/Anadyr','Moscow+08 - Bering Sea'),
	('RW',-1.57,30.04,'Africa/Kigali',''),
	('SA',24.38,46.43,'Asia/Riyadh',''),
	('SB',-9.32,160.12,'Pacific/Guadalcanal',''),
	('SC',-4.4,55.28,'Indian/Mahe',''),
	('SD',15.36,32.32,'Africa/Khartoum',''),
	('SE',59.2,18.03,'Europe/Stockholm',''),
	('SG',1.17,103.51,'Asia/Singapore',''),
	('SH',-15.55,-5.42,'Atlantic/St_Helena',''),
	('SI',46.03,14.31,'Europe/Ljubljana',''),
	('SJ',78,16,'Arctic/Longyearbyen',''),
	('SK',48.09,17.07,'Europe/Bratislava',''),
	('SL',8.3,-13.15,'Africa/Freetown',''),
	('SM',43.55,12.28,'Europe/San_Marino',''),
	('SN',14.4,-17.26,'Africa/Dakar',''),
	('SO',2.04,45.22,'Africa/Mogadishu',''),
	('SR',5.5,-55.1,'America/Paramaribo',''),
	('ST',0.2,6.44,'Africa/Sao_Tome',''),
	('SV',13.42,-89.12,'America/El_Salvador',''),
	('SY',33.3,36.18,'Asia/Damascus',''),
	('SZ',-26.18,31.06,'Africa/Mbabane',''),
	('TC',21.28,-71.08,'America/Grand_Turk',''),
	('TD',12.07,15.03,'Africa/Ndjamena',''),
	('TF',-49.211,70.1303,'Indian/Kerguelen',''),
	('TG',6.08,1.13,'Africa/Lome',''),
	('TH',13.45,100.31,'Asia/Bangkok',''),
	('TJ',38.35,68.48,'Asia/Dushanbe',''),
	('TK',-9.22,-171.14,'Pacific/Fakaofo',''),
	('TL',-8.33,125.35,'Asia/Dili',''),
	('TM',37.57,58.23,'Asia/Ashgabat',''),
	('TN',36.48,10.11,'Africa/Tunis',''),
	('TO',-21.1,-175.1,'Pacific/Tongatapu',''),
	('TR',41.01,28.58,'Europe/Istanbul',''),
	('TT',10.39,-61.31,'America/Port_of_Spain',''),
	('TV',-8.31,179.13,'Pacific/Funafuti',''),
	('TW',25.03,121.3,'Asia/Taipei',''),
	('TZ',-6.48,39.17,'Africa/Dar_es_Salaam',''),
	('UA',50.26,30.31,'Europe/Kiev','most locations'),
	('UA',48.37,22.18,'Europe/Uzhgorod','Ruthenia'),
	('UA',47.5,35.1,'Europe/Zaporozhye','Zaporozh\'ye, E Lugansk / Zaporizhia, E Luhansk'),
	('UA',44.57,34.06,'Europe/Simferopol','central Crimea'),
	('UG',0.19,32.25,'Africa/Kampala',''),
	('UM',16.45,-169.31,'Pacific/Johnston','Johnston Atoll'),
	('UM',28.13,-177.22,'Pacific/Midway','Midway Islands'),
	('UM',19.17,166.37,'Pacific/Wake','Wake Island'),
	('US',40.4251,-74.0023,'America/New_York','Eastern Time'),
	('US',42.1953,-83.0245,'America/Detroit','Eastern Time - Michigan - most locations'),
	('US',38.1515,-85.4534,'America/Kentucky/Louisville','Eastern Time - Kentucky - Louisville area'),
	('US',36.4947,-84.5057,'America/Kentucky/Monticello','Eastern Time - Kentucky - Wayne County'),
	('US',39.4606,-86.0929,'America/Indiana/Indianapolis','Eastern Time - Indiana - most locations'),
	('US',38.4038,-87.3143,'America/Indiana/Vincennes','Eastern Time - Indiana - Daviess, Dubois, Knox & Martin Counties'),
	('US',41.0305,-86.3611,'America/Indiana/Winamac','Eastern Time - Indiana - Pulaski County'),
	('US',38.2232,-86.2041,'America/Indiana/Marengo','Eastern Time - Indiana - Crawford County'),
	('US',38.2931,-87.1643,'America/Indiana/Petersburg','Eastern Time - Indiana - Pike County'),
	('US',38.4452,-85.0402,'America/Indiana/Vevay','Eastern Time - Indiana - Switzerland County'),
	('US',41.51,-87.39,'America/Chicago','Central Time'),
	('US',37.5711,-86.4541,'America/Indiana/Tell_City','Central Time - Indiana - Perry County'),
	('US',41.1745,-86.373,'America/Indiana/Knox','Central Time - Indiana - Starke County'),
	('US',45.0628,-87.3651,'America/Menominee','Central Time - Michigan - Dickinson, Gogebic, Iron & Menominee Counties'),
	('US',47.0659,-101.176,'America/North_Dakota/Center','Central Time - North Dakota - Oliver County'),
	('US',46.5042,-101.244,'America/North_Dakota/New_Salem','Central Time - North Dakota - Morton County (except Mandan area)'),
	('US',39.4421,-104.59,'America/Denver','Mountain Time'),
	('US',43.3649,-116.121,'America/Boise','Mountain Time - south Idaho & east Oregon'),
	('US',36.4708,-108.411,'America/Shiprock','Mountain Time - Navajo'),
	('US',33.2654,-112.042,'America/Phoenix','Mountain Standard Time - Arizona'),
	('US',34.0308,-118.143,'America/Los_Angeles','Pacific Time'),
	('US',61.1305,-149.54,'America/Anchorage','Alaska Time'),
	('US',58.1807,-134.251,'America/Juneau','Alaska Time - Alaska panhandle'),
	('US',59.3249,-139.434,'America/Yakutat','Alaska Time - Alaska panhandle neck'),
	('US',64.3004,-165.242,'America/Nome','Alaska Time - west Alaska'),
	('US',51.5248,-176.393,'America/Adak','Aleutian Islands'),
	('US',21.1825,-157.513,'Pacific/Honolulu','Hawaii'),
	('UY',-34.53,-56.11,'America/Montevideo',''),
	('UZ',39.4,66.48,'Asia/Samarkand','west Uzbekistan'),
	('UZ',41.2,69.18,'Asia/Tashkent','east Uzbekistan'),
	('VA',41.5408,12.2711,'Europe/Vatican',''),
	('VC',13.09,-61.14,'America/St_Vincent',''),
	('VE',10.3,-66.56,'America/Caracas',''),
	('VG',18.27,-64.37,'America/Tortola',''),
	('VI',18.21,-64.56,'America/St_Thomas',''),
	('VN',10.45,106.4,'Asia/Ho_Chi_Minh',''),
	('VU',-17.4,168.25,'Pacific/Efate',''),
	('WF',-13.18,-176.1,'Pacific/Wallis',''),
	('WS',-13.5,-171.44,'Pacific/Apia',''),
	('YE',12.45,45.12,'Asia/Aden',''),
	('YT',-12.47,45.14,'Indian/Mayotte',''),
	('ZA',-26.15,28,'Africa/Johannesburg',''),
	('ZM',-15.25,28.17,'Africa/Lusaka',''),
	('ZW',-17.5,31.03,'Africa/Harare','');
The above method relies on the minimum distance between the GPS coordinate and the timezone center which therefore incorrect for time zone boundary regions. A better approach may using external services (See http://stackoverflow.com/questions/41504/timezone-lookup-from-latitude-longitude%E2%80%8C%E2%80%8B). Another way not relying on external Web services is using time zone shape information (See http://efele.net/maps/tz/world/). We may revisit this part later.
Attachment: