Working with multiple UTC/GMT timezones in MySQL

|
When working with international timezones it is not always possible to work off UTC/GMT time. At times the webhost may prevent you from changing configuration settings or data may already exist that prevent you from updating the system timezone.

Luckily with MySQL's date functionality it is not too hard to work around this problem. In this MySQL tip I will show how it is possible to work with GMT timezones for any record with a little date manipulation. Please note that I assume the dates you are working with are recognized MySQL date formats of either DATE, DATETIME, or TIMESTAMP.

The first thing that you need to do is find out the time offset between your database and GMT. You can do this by using the query SELECT NOW() and checking the difference using a Google query. As an example the server I am working on shows the time as "2009-03-16 01:35:15" while Google tells me the current GMT time is "6:35am Monday". This means the GMT offset of the server is +5 hours (6 - 1).

The second thing that you need is for your users to tell you what their GMT offset is. For example I am on the Australian east coast so my GMT offset is +10. For demonstration purposes I will assume this value is stored in the "settings" table in the field "gmt_offset". Using this two bits of information we can get the user's local time by using the following query.




SELECT
DATE_ADD(NOW(), INTERVAL 5 HOUR) as gmt_time,
DATE_ADD(NOW(), INTERVAL (5 + gmt_offset) HOUR) as local_time
FROM
settings
WHERE
settings.user = 'Paul'

A more complex example where manual time offsetting is required is for time related user notifications such as in a calendar application. In this example I will show how a notification table where a user set notification time is stored in the server's default time can be used so that we can retrieve notifications that need to be sent out correctly.

SELECT
n.*,
DATE_ADD(NOW(), INTERVAL (5 + s.gmt_offset) HOUR) as local_time_start,
DATE_ADD(DATE_ADD(NOW(), INTERVAL (5 + s.gmt_offset) HOUR), INTERVAL 1 MINUTE) as local_time_end
FROM
notifications AS n
LEFT OUTER JOIN settings AS s ON (n.user = s.user)
HAVING
local_time_start <= n.notify_at
AND n.notify_at < local_time_end

The trick with the above query is we are treating notify_at as the user would expect, in the user's local time, therefore instead of adjusting it to the server time we simply calculate what the user's local time is at the current server time and see if any users need to have their notifications sent within this minute. While it is possible to shift the notification time to the server time and retrive required notification records based on that I find it more intuitive to leave user input as is.

Using the above method means that the system does not actually need to worry about any timezone issues unless the system in actioning a user set timezone as we can expect all users to work within their own. However such a method can mean more complications if your application's users are allowed to share date related information, in which case 3 offsets need to be combined (server offset, sharer offset, sharee offset) to show an accurate local time.

0 comments:

Post a Comment