Bug #99621 TIME_FORMAT microsecond parsing omitted

• Submitted: 18 May 2020 23:58
• Modified: 17 Feb 2021 13:43
• Reporter: Daniel Black
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.6.48, 8.0.20, 5.7.22
• OS: Any
• CPU Architecture: Any

[18 May 2020 23:58] Daniel Black

Description:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_time-format allows for hour/minute/second/microsecond.

part 1: Microseconds are unparsed.

SELECT TIME_FORMAT("0:1:38:096", "%H:%i:%s:%f")
-> "00:01:38:000000"

part 2: Without hour ("%H") and with "%f" TIME_FORMAT returns null

SELECT TIME_FORMAT("1:38:096", "%i:%s:%f")
-> NULL

noticed thanks to https://stackoverflow.com/questions/61880227/cast-a-varchar-to-minutes-seconds-and-millise...

How to repeat:

SELECT TIME_FORMAT("0:1:38:096", "%H:%i:%s:%f")
-> "00:01:38:000000"

SELECT TIME_FORMAT("1:38:096", "%i:%s:%f")
-> NULL

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3292799fc6f8674fb15f14c7b2b8eb47

Suggested fix:

SELECT TIME_FORMAT("0:1:38:096", "%H:%i:%s:%f")
-> 00:01:38.096000

SELECT TIME_FORMAT("1:38:096", "%i:%s:%f")
-> 00:01:38.096000

Like what SELECT STR_TO_DATE("1:38:096", "%i:%s:%f") returns

[19 May 2020 5:18] MySQL Verification Team

Hello Daniel,

Thank you for the report and feedback.

regards,
Umesh

[24 Jul 2020 18:07] Martin Hansson

Hi Daniel,
this is expected behavior. Consider the warning:

mysql> SELECT TIME_FORMAT("0:1:38:096", "%H:%i:%s:%f"); SHOW WARNINGS;
+------------------------------------------+
| TIME_FORMAT("0:1:38:096", "%H:%i:%s:%f") |
+------------------------------------------+
| 00:01:38:000000                          |
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '0:1:38:096' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

In fact, if you first create a time literal, you get an error up front:

mysql> SELECT TIME_FORMAT(TIME"0:1:38:096", "%H:%i:%s:%f");
ERROR 1525 (HY000): Incorrect TIME value: '0:1:38:096'

But TIME_FORMAT is a little nicer when called with a string.

Correct format for fractional seconds is to use a dot before the fractional part, like so:

mysql> SELECT TIME_FORMAT("0:1:38.096", "%H:%i:%s:%f");
+------------------------------------------+
| TIME_FORMAT("0:1:38.096", "%H:%i:%s:%f") |
+------------------------------------------+
| 00:01:38:096000                          |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIME_FORMAT(TIME"0:1:38.096", "%H:%i:%s:%f");
+----------------------------------------------+
| TIME_FORMAT(TIME"0:1:38.096", "%H:%i:%s:%f") |
+----------------------------------------------+
| 00:01:38:096000                              |
+----------------------------------------------+
1 row in set (0.00 sec)