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
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.
order by Name
This gives a global list of timezones from
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/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);
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.
TIMESTAMPdata 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
TIMESTAMPcoiumns, you have solved the time zone problem.
DATETIMEdata doesn’t take time zones into account. Rick James once said
DATETIMEvalues 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
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
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
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
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.