Bug #68061 Temporal functions truncate instead of round
Submitted: 9 Jan 2013 20:42 Modified: 22 Feb 2013 4:21
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.6.8-rc OS:Any
Assigned to: CPU Architecture:Any

[9 Jan 2013 20:42] Todd Farmer
Description:
In MySQL 5.5, certain temporal functions employed rounding when dealing with data exceeding bounds of the data type involved:

mysql> SELECT SEC_TO_TIME(2378.3422349);
+---------------------------+
| SEC_TO_TIME(2378.3422349) |
+---------------------------+
| 00:39:38                  |
+---------------------------+
1 row in set (0.06 sec)

mysql> SELECT SEC_TO_TIME(2378.6422349);
+---------------------------+
| SEC_TO_TIME(2378.6422349) |
+---------------------------+
| 00:39:39                  |
+---------------------------+
1 row in set (0.00 sec)

That is not the case with fractional seconds in 5.6, which instead simply truncates the digits beyond precision allowed:

mysql> SELECT SEC_TO_TIME(2378.6422349);
+---------------------------+
| SEC_TO_TIME(2378.6422349) |
+---------------------------+
| 00:39:38.642234           |
+---------------------------+
1 row in set (0.00 sec)

This may be intentional, but the manual is unclear on this topic:

Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value. The range of the result is constrained to that of the TIME data type. A warning occurs if the argument corresponds to a value outside that range. 

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_sec-to-time

There is, however, no warning related to loss of precision.

In some cases, truncation (and not rounding) seems entirely appropriate:

mysql> SELECT NOW(), NOW(3), NOW(4);
+---------------------+-------------------------+--------------------------+
| NOW()               | NOW(3)                  | NOW(4)                   |
+---------------------+-------------------------+--------------------------+
| 2013-01-09 12:37:11 | 2013-01-09 12:37:11.640 | 2013-01-09 12:37:11.6406 |
+---------------------+-------------------------+--------------------------+
1 row in set (0.00 sec)

How to repeat:
See above.

Suggested fix:
Clarify when data is truncated and when it is rounded.
[10 Jan 2013 12:26] Paul Dubois
Shouldn't this be categorized as a server bug so that a developer can look at it and determine whether it's intentional?
[30 Jan 2013 14:16] Øystein Grøvlen
Built-in SQL function SEC_TO_TIME does not handle fractional
seconds correctly. If there are more than six decimals, 
result is truncated instead of rounded.

Solution: In function sec_to_time(), do rounding instead of truncation.
[22 Feb 2013 4:21] Paul Dubois
Noted in 5.6.11, 5.7.1 changelogs.

For arguments with fractional seconds greater than six decimals,
SEC_TO_TIME() truncated, rather than rounding as it should have.