Correctly Handling Timestamp Arithmetic in MySQL
10 April 2013
Working with dates and times in databases, and even software in general, can be tricky. Different timezones and formats to parse are all pitfalls waiting to trip up the unwary engineer. MySQL is a widely used database and in its default configuration is very forgiving. This certainly contributes to its popularity but unfortunately lays some traps for those of us who are careless.
I was tripped up by MySQL's leniency when I tried to insert a value representing an expiry time into a column in the database. This was a time in the future so I wanted to calculate it from the current time, plus some arbitrary number of seconds. I fired up the mysql console and, using the
test database, tried this:
mysql> CREATE TABLE `test` (`time` DATETIME); mysql> INSERT INTO `test` VALUES(CURRENT_TIMESTAMP + 10); mysql> SELECT * FROM `test`; +---------------------+ | time | +---------------------+ | 2013-04-07 11:49:52 | +---------------------+ 1 row in set (0.01 sec)
All looks to be working fine; that was easy! It was only when there started to be intermittent errors in the application that I had to look a little more closely...
It turns out that this works fine when the integer that you add doesn't cause the number of seconds to go above 60 i.e. into the next minute. For example, adding
10 when the current time is
2013-04-07 11:49:42 gives
2013-04-07 11:49:52, which is correct. However, if the current time were
2013-04-07 11:49:52 and we added
10 to it we get something invalid which is then coerced into a
DATETIME column. In debugging this I ran the
SELECT in a loop until I saw the problem, like this:
$ while true; do echo 'INSERT INTO `test` VALUES(CURRENT_TIMESTAMP + 10); \ SELECT * FROM `test`;' | mysql -u root test; sleep 5; done ... time 2013-04-07 12:11:47 2013-04-07 12:11:53 2013-04-07 12:11:58 0000-00-00 00:00:00 0000-00-00 00:00:00 2013-04-07 12:12:13 2013-04-07 12:12:19
It appeared to be once the current time got to the end of a minute that the value suddenly appears as
0000-00-00 00:00:00. Looking more closely at what's happening before it goes into the column it looks something like this:
$ echo 'SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + 10' | mysql -u root test CURRENT_TIMESTAMP CURRENT_TIMESTAMP + 10 2013-04-07 12:22:51 20130407122261.000000
For some reason the timestamp is reformatted to remove all the spaces, dashes and colons and the seconds part - the two digits just to the left of the decimal point - is now invalid:
61. When an invalid date is inserted into a
DATETIME column, by default, MySQL will convert it to
0000-00-00 00:00:00 and generate a warning (it's mentioned in the SQL mode documentation under ALLOW_INVALID_DATES, but more on SQL mode later).
Use the Date/Time Functions
The solution here is to use a proper function to do the arithmetic on the timestamp value -
TIMESTAMPADD would work; run
help <function name> in a
mysql prompt to get usage instructions.
Another possibility is to use the
INTERVAL keyword along with the appropriate date part, in this case
mysql> INSERT INTO `test` VALUES(CURRENT_TIMESTAMP + INTERVAL 10 SECOND);
Guarding Against it with SQL Mode
It seems here that MySQL is too lenient with what it'll accept. It would have been better to throw an error and not accept the invalid date at all. Fortunately, the server can be configured to do that by setting the SQL mode. Running the following will prevent the invalid date from ending up in the database at all:
mysql> SET SESSION sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec)
So now (for this session at least) when we try to insert an invalid date:
mysql> INSERT INTO `test` VALUES(CURRENT_TIMESTAMP + 60); ERROR 1292 (22007): Incorrect datetime value: '20130410073762' for column 'time' at row 1
The mode can be set globally in the MySQL config file:
my.ini, depending on your system. Exactly what mode you use will depend on how you're using your database.