About

The eLog library was initially developed as a research prototype and later published for lifelogging researchers in 2010 to help them easily analyze heterogenous data and complex visualization. It has been kept growing with the progress of mobile computing environments and recently its UI part is released with GPL v3 license for wider usage. The eLog UI library is optimized for mobile environment and can get easily integrated with existing Web services.

Who We Are

The original work was proposed by Pil Ho and later extended the work with collaboration with 28 researchers around the world who contributed their lifelogs, collaborated for lifelog analysis and share research results to build up an open lifelogging platform for the public. Pil Ho has been keeping the development updating the library following up the progress in mobile computing.

Updates

  • Nov. 2014: Change the web page skin using bootstrap.
  • Nov. 2014: Published elog UI library as GPL v3.
  • Oct. 2014: Version up eLog library and documentation.

 

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: