5

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.

0

1 Answer 1

1

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.

1
  • Thanks. Yes, I agree with you, there was something bad in earlier version, but I was trying to recreate the "wrong behaviour" so I could keep working without changing many queries. Feb 14, 2017 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.