Bug #36760 from_unixtime rounds fractional seconds
Submitted: 16 May 2008 15:31 Modified: 17 May 2008 19:07
Reporter: Dan Gunter Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.45 MySQL Community Server OS:Any
Assigned to: CPU Architecture:Any
Tags: date, fractional

[16 May 2008 15:31] Dan Gunter
Description:
Using from_unixtime() on values with fractional seconds gives incorrect results because fractional seconds are rounded to the nearest whole second.

How to repeat:
set time_zone = '-00:00';
select from_unixtime(86399.5);
select from_unixtime(86399.4);

-- 

+------------------------+
| from_unixtime(86399.5) |
+------------------------+
| 1970-01-02 00:00:00    | 
+------------------------+
1 row in set (0.00 sec)

+------------------------+
| from_unixtime(86399.4) |
+------------------------+
| 1970-01-01 23:59:59    | 
+------------------------+
1 row in set (0.00 sec)

Suggested fix:
Ideally, the fractional seconds should not be dropped at all; but at the minimum they should be truncated and not rounded. Otherwise, this incorrectly advances to the next whole second at or after 0.5 seconds in the previous whole second, causing for example the day to advance 0.5 seconds early.
[16 May 2008 17:25] Valeriy Kravchuk
Thank you for a problem report. I can repeat the behaviour described on 5.0.60, but why do you think it is a bug? Unix time is measured in whole seconds (see http://en.wikipedia.org/wiki/Unix_time, for example), so your value is converted to integer before conversion to real datetime. And rounding is normal for decimal to integer conversion.
[16 May 2008 18:47] Dan Gunter
It is a bug because the correct second is the last whole second, not the "nearest second", which is what rounding gets you. Imagine it is 1:00:00 ; 3/4 of a second pass; would you then say that the current time is 1:00:01 ? No, it's still 1:00:00 and 0.75 seconds; in 0.25 seconds then it will be 1:00:01.

It also helps to note that no other time units work this way. Is the DAY(8pm on Tuesday) = Wednesday? Is the MONTH(May 17) = June? No, the measure used in all other conversions is not "nearest", but "previous whole unit". Seconds should not be any different.
[17 May 2008 19:07] Sveta Smirnova
Thank you for the feedback, but according to http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime:

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.

...

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer.

So FROM_UNIXTIME expects integer and conversion is correct. As workaround you can use select from_unixtime(floor(86399.5));