Good things happen when you subtract datetimes in MySQL
Friday, 28 February 2014
Of course, you know that “good things” and “MySQL” don’t go together. File this one under the category of “small ways in which MySQL is broken”.
Let’s fire up MySQL 5.1.72-2-log or 5.5.34-log.
[code]mysql> create temporary table blah
-> (alpha datetime, beta datetime);
Query OK, 0 rows affected (0.01 sec)
mysql> describe blah;
+——-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+——-+
| alpha | datetime | YES | | NULL | |
| beta | datetime | YES | | NULL | |
+——-+———-+——+—–+———+——-+
2 rows in set (0.00 sec)[/code]
OK, so we have two datetimes in a table. Let’s try adding a row:
[code]mysql> insert into blah (alpha, beta)
-> VALUES (‘2014-01-01 03:00:00’, ‘2014-01-01 03:00:37’);
Query OK, 1 row affected (0.00 sec)[/code]
What happens if we try subtracting two datetimes?
[code]mysql> select alpha, beta, beta – alpha from blah;
+———————+———————+————–+
| alpha | beta | beta – alpha |
+———————+———————+————–+
| 2014-01-01 03:00:00 | 2014-01-01 03:00:37 | 37.000000 |
+———————+———————+————–+
1 row in set (0.00 sec)[/code]
So we got the number of seconds between the two datetimes. Let’s try that again with two datetimes a minute apart.
[code]mysql> insert into blah (alpha, beta)
-> VALUES (‘2014-01-01 03:00:00’, ‘2014-01-01 03:01:00’);
Query OK, 1 row affected (0.00 sec)
mysql> select alpha, beta, beta – alpha from blah;
+———————+———————+————–+
| alpha | beta | beta – alpha |
+———————+———————+————–+
| 2014-01-01 03:00:00 | 2014-01-01 03:00:37 | 37.000000 |
| 2014-01-01 03:00:00 | 2014-01-01 03:01:00 | 100.000000 |
+———————+———————+————–+
2 rows in set (0.00 sec)[/code]
So, 100 seconds in a minute? Yikes. Obviously, this isn’t how you’re supposed to subtract datetimes in MySQL. But the great part is that it kind of works! You get a number back that correlates to the actual interval of time between the two, and if you’re measuring lots of small intervals, you might not notice that your data is between 100% and 167% of what it should be. Excellent puzzle to drive a junior dev crazy!
Wait, any reasonable database would have known that we were making a mistake, right?
[code]mysql> show warnings;
Empty set (0.00 sec)[/code]
No. 1 — March 15th, 2014 at 1:54 pm
The underlying problem here is that a datetime is stored as a number under the hood. It’s a 64-bit unsigned integer encoded in a silly ASCII-like representation of the datetime. If you look at the data itself, you’ll find that “2014-01-01 03:00:00” is stored as the number — can you guess it — 20140101030000. Least brilliant idea ever, but it “works” until you do something you’re not supposed to, as you’ve done. Same is true of the date type.
You’re supposed to use the date and time functions to perform math on these types, not the built-in math operators :-/
No. 2 — June 9th, 2014 at 12:46 am
You need to treat dates and times separately, would be nice to have a single function do this, but it can be solves partially like this:
select alpha, beta, datediff(alpha, beta), timediff(alpha, beta) from blah;
No. 3 — June 23rd, 2014 at 1:08 am
“You’re supposed to use the date and time functions to perform math on these types, not the built-in math operators :-/”
A good database engine either prevents these errors or handles the operators in true polymorphic fashion, as with PostgreSQL (note that the return value is typed correctly):
postgres=# select ‘2014-01-01 03:01:00’::timestamp – ‘2014-01-01 03:00:00’::timestamp;
?column?
———-
00:01:00
(1 row)