UTC Timezone

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 time_zone CSV file. And then create the below stored procedure. The usage is straight-forward from the source.

CREATE DEFINER=`povi`@`%` FUNCTION `get_timezone`(
        fLatitude DOUBLE,
        fLongitude DOUBLE
    ) RETURNS varchar(255) CHARSET latin1
    DETERMINISTIC
BEGIN

RETURN
(SELECT
    tz.zonename
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
);

END;