How to save future(!) dates in a database

Question

This question is specifically about future dates and times (for past values UTC is undoubtedly the first choice).

---

I'd wonder if anybody had a suggestion as to the "best" way to save a future date and time in a MySQL database (or for that matter also generally), particularly in a context where the column can hold times from different timezones. Considering that timezone rules may change UTC might not be the best option.

The most feasible option I found so far would be the save it as text in the location's local time, together with the location (eg. "America/*"). Unfortunately this solution might be more prone to data corruption and is definitely less convenient for calculations.

Could anybody think of something better?

Thanks in advance

Answer 1

First, I've written about this in extensive detail before, so please read my answers here and here, as well as this blog post by Lau Taarnskov.

With specific regard to MySQL, you generally don't want to use a TIMESTAMP field for the local time of a future event, as it will convert from the session's time zone to UTC at write time, and convert back from UTC to the session's time zone at read time. Even if these are the same time zone ids (which they don't have to be), there's no guarantee that the time zone data won't change for one or both of the time zones between when you write the data and when the event takes place.

Instead, use a DATETIME field, which does no implicit time zone conversions. You get the exact value out that you wrote. Store the local time of the event, and store a VARCHAR field containing the time zone identifier for the event. This is the only way to retain the user's original intent.

Rationale and edge cases are all described in the answers I gave previously.

Answer 2

The considerations for saving future datestamps are pretty much the same as for past datestamps.

(I call them datestamps because both DATETIME and TIMESTAMP are reserved words in MySQL. For the sake of discussion I want a word that doesn't imply either data type.)

If you're building a system to be used by people in multiple time zones, it's a good idea to ask each user for her time zone preference, and store it in her user profile. Then, when she logs in you can retrieve it, then do

  SET time_zone = 'America/Halifax'

or whatever, naming the user's time zone preference.

If your MySQL server is running on a Linux, BSD, or other *nix system, these time zones come from the zoneinfo subsystem on that machine. zoneinfo gets updated when various national jurisdictions change time zone rules. The people who maintain popular distros routinely push updates to zoneinfo, so you'll be reasonably up to date. (If your MySQL server is running on a Windows host, do some reading about MySQL time zone stuff on that OS. It's more of a hassle to keep things up to date.)

Then, 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 timezone.

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(), 'America/Halifax', 'UTC') + INTERVAL 7 DAY
  WHERE id = something

SELECT CONVERT_TZ(datestamp, 'UTC', 'America/Halifax') datestamp
  FROM appointment
 WHERE id = something

Obviously, substitute your user's choice of timezone for 'America/Halifax' in these queries.

IF YOU POSSIBLY CAN AVOID IT don't store your datestamps with reference to a local time that changes from daylight savings to standard time. If you do that, you will have ongoing glitches on the changeover days for the lifetime of your application. I know this because I've inherited a couple of systems that worked that way. Bad idea. UTC: good idea.