The witching hour: How illegal datetimes get stored into MySQL in US timezones

Most companies make a mistake at the very beginning.  They set their system time zone to Eastern, Central, Mountain, or Pacific time.  Then, they install their database on it.  They use all manner of timestamp and date types and insert terabytes of data.  After that, it becomes almost impossible (in terms of being worth the effort) to change it some months or years later when the company has a popular product or service.  One of the unfortunate issues that may arise is illegal datetimes.

On Sunday, March 11, 2018, when US time zones switched To daylight savings time, there was no 2AM hour.  When we switch back to normal time on Sunday November 4,  there will be two 2AM hours.

Inevitably, out of many database servers and many different types of application servers, some data manages to get inserted into MySQL with an illegal datetime during the 2AM hour on the “spring forward” date.  Each year, developers ask the question about application errors arising from it.

Here is an illustration of how it happens.

Given the following table definition:

CREATE TABLE `ts_test` (
  `js_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `js_dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Let’s now try to insert some data.

mysql> insert into ts_test (js_ts,js_dt) values ('2018-03-11 02:12:00','2018-03-11 02:12:00');
ERROR 1292 (22007): Incorrect datetime value: '2018-03-11 02:12:00' for column 'js_ts' at row 1

We got an error for the datetime column.  This is good right?  MySQL is protecting us from inserting a bad value.  So – if I’m running… let’s see here, what am I running.  MySQL 5.7.21 — there should be no issue, Right?  Maybe this was yesterday’s issue on a different version of MySQL?….

Not so fast.

What about your ORM?  Is your ORM giving any specific instructions to the database for inserts and updates to datetimes?  If so, maybe… something like THIS is happening.

mysql> set session time_zone='-5:00';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into ts_test (js_ts,js_dt) values ('2018-03-11 02:12:00','2018-03-11 02:12:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ts_test;
+---------------------+---------------------+
| js_ts               | js_dt               |
+---------------------+---------------------+
| 2018-03-11 02:12:00 | 2018-03-11 02:12:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Hm.  So, if we set the session time zone to UTC -5, the insert succeeds.  That must mean that MySQL has made this “right”.  If I query again after disconnecting from this session and starting a fresh one in daylight savings time – what will happen?  Both timestamps will advance and show 3AM…. right?

mysql> select * from ts_test;
+---------------------+---------------------+
| js_ts               | js_dt               |
+---------------------+---------------------+
| 2018-03-11 03:12:00 | 2018-03-11 02:12:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Wrong.

The timestamp column adjusted.  The datetime one did not.  I now have a situation that my application is not going to like.  My applications above this database will error on the js_dt column value because it’s an illegal datetime.

What if I open up a virtual machine, create the same table, set the system time back to just before the time change, establish a MySQL session to the database server, leave that connection open, and continue to insert rows as the time changes.

I ran:

~# date -s "11 Mar 2018 01:59:30";
> insert into ts_test values (now(),now()); # repeatedly

… and as we went over the time change, MySQL did the right thing with the now() function and the session as far as I could tell.

| 2018-03-11 01:59:55 | 2018-03-11 01:59:55 |
| 2018-03-11 01:59:57 | 2018-03-11 01:59:57 |
| 2018-03-11 01:59:58 | 2018-03-11 01:59:58 |
| 2018-03-11 01:59:59 | 2018-03-11 01:59:59 |
| 2018-03-11 03:00:00 | 2018-03-11 03:00:00 |
| 2018-03-11 03:00:01 | 2018-03-11 03:00:01 |
| 2018-03-11 03:00:02 | 2018-03-11 03:00:02 |
| 2018-03-11 03:00:03 | 2018-03-11 03:00:03 |
| 2018-03-11 03:00:04 | 2018-03-11 03:00:04 |

I did not have applications at hand to try a PHP session or a java session inserting data second by second as we passed over the time change.

Most of the time we see this in the real world, the datetime datapoints are only few seconds past 2AM.  It is my belief that ORMs and software in general can set a specific offset from UTC, set it in the database session, and fail to change it because of an operation taking a few seconds and completing after the time change.

Let’s recap.

Q: How do bad datetime values get into the database?

  • Because application ORMs and other session originators can explicitly set the session offset from UTC.
  • Because a session connected prior to the time change may still have the previous time zone setting.

Q: Why is this a problem for datetimes and *not* timestamps?

  • The underlying storage for timestamps is UTC, and times must be greater than the start of the epoch (1970-01-01 00:00:00) or 0 unixtime.  The DB converts timestamps to UTC, and that conversion works relative to the connected session time zone.
  • Datetimes are not converted back and forth.  What you put in is what you get back out.  MySQL does try to validate datetimes based on the current timezone (see previous error), but an override of session.time_zone to an explicit UTC offset will negate the comparison to the system time zone; thus allowing “illegal” values to be inserted.

Q: What do I do if I have illegal datetimes in my database?

  • Illegal datetimes should only occur during 1 hour per year, therefore it should be easy to query to look for and report on them.  They should be updated + 1 hour.

One thought on “The witching hour: How illegal datetimes get stored into MySQL in US timezones

  1. The post title makes reference to the US but this is an issue for anyone using a timezone which has daylight saving time other than UTC.

    Part of the problem I see is that _if_ you have set the server timezone to something other than UTC inserting UTC values is quite hard unless you use the set session time_zone=’XX:XX’; but that requires an extra query and round trip to be sure things are working properly. Modifying an app to use UTC values explicitly is pretty hard if you run a service 24×7. It’s also not possible to insert the UTC timestamp values explicitly or retrieve the data “unconverted”. See: https://bugs.mysql.com/83852 which is a feature request I filed “recently”, even if the issue goes back in MySQL forever.

    Let us hope that this functionality will appear then the migration to make the application insert using UTC values and retrieve UTC values means that the problem can be removed in small steps.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s