Time Zones in MySQL

by

Suppose you’re working on a web application to support users in multiple time zones. How do you handle dates and times? If your users all live in one time zone, it’s easy. Simply store important date and time data in DATETIME or TIMESTAMP columns, and be done with it.

But, what application has users in just one time zone? Maybe a municipal web site can get away with that assumption. The rest of us have to deal with time zones.

So, it’s helpful to understand how to set up a web app for that purpose.

How do you know a user’s preferred time zone?

Ask her. Offer a way for each user to select her preferred time zone from a dropdown list.

Set up a preferred_timezone column in your user table. It should have the data type varchar(63) to be future proof.

In most MySQL implementations, you can issue the following query to get a list of posssible time zones. That’s how to populate your application’s dropdown list.


select Name
from mysql.time_zone_name
order by Name

This gives a global list of timezones from Africa/Abidjan to Zulu. (The latter doesn’t refer to an indigenous southern African nation. Instead it’s a synonym for Universal Time Coordinated. Another time zone name for that time zone is UTC, a time zone name with which we’ll get familiar.)

Most of the Names in this column take the form Continent/City. For example, you’ll find America/New_York for United States Eastern Time and Asia/Kolkata for India Time.  Those are the best choices for recording your users’ preferred time zones. MySQL can use them to handle both standard time and daylight savings time correctly.

Using these time zone names is a good idea for another reason. Some juristidictions have complex time zone rules because different towns want to use different time zones. The US state of Indiana is an example. (Indiana is the state in which the legislature, in 1897, seriously considered legislation establishing the value of 𝜋 as 3.) They offer several time zone names like America/Indianapolis and America/Indiana/Vincennes. People in Indiana are accustomed to choosing the appropriate time zone, and nobody else cares very much. So, offer your users the list of names to choose time zones.

Once you know your user’s preferred time zone, use it to set MySQL’s time_zone setting. Repeat that operation each time you start handlng data from MySQL on behalf of the user. That might look like this, assuming your user table has a preferred_timezone column and you’ve stored user preferences there.


SELECT @user_timezone := (SELECT preferred_timezone FROM user WHERE id=$userId);
SET time_zone=@user_timezone;

Tip: the mysql.time_zone_name table has an Id column in it. Do not use those Id values to represent time zone choices; they can change.

Storing date and time information

When you design tables to hold date and time information, you have two basic choices of data type.

  • TIMESTAMP data is the best choice in many cases. When you retrieve data from columns with that data type, MySQL automatically translates it into the time zone for the current setting of time_zone. When you store data into those columns, MySQL automatically translates it to UTC.  In other words, when you handle user preference for time zones correctly and use TIMESTAMP coiumns, you have solved the time zone problem.
  • DATETIME data doesn’t take time zones into account. Rick James once said DATETIME values are like photographs of clocks. They’re just snapshots of local time. But, they have the advantage of being able to represent a broader range of calendar dates than TIMESTAMPs.

Using TIMESTAMP values

If you use TIMESTAMP data types for your datestamps, any time you retrieve a value, it is automatically translated from UTC to the local timezone before display. Any time you store a value it is automatically translated to UTC. The NOW() value is timestamp-like in this respect. So if you, for example, do

 UPDATE appointment
    SET datestamp = NOW() + INTERVAL 7 DAY
  WHERE id = something

you’ll store a UTC time that’s a week after this moment. Then if you do

SELECT datestamp
  FROM appointment
 WHERE id = something

the user will see the time in her local timezone as set with SET time_zone.

Using DATETIME values

If you use DATETIME data types for your datestamps, you can offset them yourself when you store and retrieve them. When you store them, offset them from the local timezone to UTC. When you retrieve them, go the other way. Use CONVERT_TZ() for that.

 UPDATE appointment 
    SET datestamp = CONVERT_TZ(NOW(), @user_timezone, 'UTC') + INTERVAL 7 DAY
  WHERE id = something

SELECT CONVERT_TZ(datestamp, 'UTC', @user_timezone) datestamp
  FROM appointment
 WHERE id = something

Obviously, make sure your user’s choice of timezone is available in the MySQL session variable called @user_timezone.

Important design tip

When designing an application, do your best to make sure all date and time values are stored with respect to Universal Time Coordinated (the UTC or Zulu time zone, formerly known as GMT. Do your best to avoid storing date and time values with respect to a local time zone with daylight savings time, or you’ll have glitches on the changeover days.

How does this work?

UNIX-style operating systems (such as Linux, BSD, and Solaris) have a built-in zoneinfo database. MySQL is integrated with that database. TIMESTAMP values are stored, in MySQL tables, as Unix-style timestamps; MySQL uses the UNIX-style time functions to handle them.

This is very convenient: most operating system distributions (Ubuntu, FreeBSD, and the rest) offer routine updates to the zoneinfo database as part of their update stream. Various jurisdictions occasionally change their time zone rules. When that happens, the global Internet Assigned Numbers Authority updates their centralized registry of time zone rules; The distributions pick up those changes and put them into their update streams.

If your MySQL server is hosted on Windows, you cannot take advantage of the automatic zoneinfo updates. The person operating your MySQL server will need to fetch and upload zoneinfo changes manually.

Leave a Comment