Mysql – TIME Function changes between 5.5 and 5.6

functionsMySQLmysql-5.5mysql-5.6

I'm facing an issue, where I can't get the same result with the TIME function when migrating from MySQL 5.5.46 to MySQL 5.6.34.
The thing is, when I use the TIME function in 5.5, it returns NULL when datetime is '0000-00-00 00:00:00', but only if it's that value comming from the table, but with 5.6 I'm getting '00:00:00'. It doesn't sound bad, but there's many queries that are relying in the behavior from 5.5 (Result is NULL).
Here's an example I did to test exactly that behavior:

First, the table:

CREATE TABLE `prueba_time` (
  `check_in` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then I insert one row with value 0000-00-00 00:00:00

mysql> INSERT INTO prueba_time VALUES ('0000-00-00 00:00:00');

And then I test the TIME function… The result in 5.5 is:

mysql> SELECT TIME(check_in),check_in,TIME('0000-00-00 00:00:00') FROM prueba_time;
+----------------+---------------------+-----------------------------+
| TIME(check_in) | check_in            | TIME('0000-00-00 00:00:00') |
+----------------+---------------------+-----------------------------+
| NULL           | 0000-00-00 00:00:00 | 00:00:00                    |
+----------------+---------------------+-----------------------------+

And the same query in 5.6:

mysql> SELECT TIME(check_in),check_in,TIME('0000-00-00 00:00:00') FROM prueba_time;
+----------------+---------------------+-----------------------------+
| TIME(check_in) | check_in            | TIME('0000-00-00 00:00:00') |
+----------------+---------------------+-----------------------------+
| 00:00:00       | 0000-00-00 00:00:00 | 00:00:00                    |
+----------------+---------------------+-----------------------------+

So… different result and I can't find out why…

The sql_mode it's different. In 5.5 it's empty, and in 5.6 I'm using STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION but according to what I've read there's no affecting in this. I hope you can help me with this.

Best Answer

I cannot see a problem here. If anything, I would say the problem was with the older behaviour, because it was just inconsistent. Depending on whether '0000-00-00 00:00:00' was passed as a literal or as a column value, TIME would return you either a 00:00:00 or a null. Since version 5.6, however, it consistently returns 00:00:00.

So, to answer why the behaviour changed, the previous behaviour was buggy and the new one is (more) consistent.

Related Question