Bug #68819 DATE_FORMAT returns empty result

• Submitted: 30 Mar 2013 15:44
• Modified: 2 Apr 2013 6:56
• Reporter: Bastiaan van Weerd
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.77
• OS: Linux
• CPU Architecture: Any
• Tags: 00:00, date_format, SEC_TO_TIME, TIME_TO_SEC

[30 Mar 2013 15:44] Bastiaan van Weerd

Description:
The DATE_FORMAT function returns 00:00 when times are subtracted in the value field as follows:

"SELECT DATE_FORMAT( SEC_TO_TIME( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) ) , '%H:%i' )"

How to repeat:
"SELECT DATE_FORMAT( SEC_TO_TIME( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) ) , '%H:%i' )"

[31 Mar 2013 18:18] Hartmut Holzgraefe

The problem is that SEC_TO_TIME returns a TIME string, DATE_FORMAT expects a DATETIME string though ... this combined with the fact that in the DATE part of a DATETIME string each punctuation character is interpreted as a valid separator, not only '-', leads to '01:00:00' being interpreted as the equivalent of 01-00-00, or actually 2001-00-00 in four-digit-year format.

You have two options to work around this:

* use FROM_UNIXTIME() instead of SEC_TO_TIME() to get a full DATETIME string, this only works when setting the time_zone variable to '+00:00' though ...

  SET time_zone='+00:00'; SELECT DATE_FORMAT( FROM_UNIXTIME( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) ) , '%H:%i' );

* add a call to CONCAT to the mix to add a dummy DATE string in front of the SEC_TO_TIME() result before passing it to DATE_FORMAT:

  SELECT DATE_FORMAT( CONCAT('1970-01-01 ', sec_to_time( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) )) , '%H:%i' );

[31 Mar 2013 18:20] Hartmut Holzgraefe

Maybe the "every punctiation character" rule for date strings should be changed to "every punctuation character but ':'" to prevent this kind of ambiguities?

[2 Apr 2013 6:56] MySQL Verification Team

Result looks okay on 5.6?

Results of different versions:

mysql> SELECT DATE_FORMAT( SEC_TO_TIME( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) ) , '%H:%i' ) a,version();
+-------+-----------+
| a     | version() |
+-------+-----------+
| 01:00 | 5.6.10    |
+-------+-----------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT( SEC_TO_TIME( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) ) , '%H:%i' ) a,version();
+-------+-----------+
| a     | version() |
+-------+-----------+
| 00:00 | 5.5.30    |
+-------+-----------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT( SEC_TO_TIME( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) ) , '%H:%i' ) a,version();
+-------+------------------+
| a     | version()        |
+-------+------------------+
| 00:00 | 5.1.68-community |
+-------+------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT( SEC_TO_TIME( TIME_TO_SEC( '13:00:00' ) - TIME_TO_SEC( '12:00:00' ) ) , '%H:%i' ) a,version();
+-------+------------------+
| a     | version()        |
+-------+------------------+
| 00:00 | 5.0.96-community |
+-------+------------------+
1 row in set (0.00 sec)