MySQL Bugs: #36760: from_unixtime rounds fractional seconds

Bug #36760	from_unixtime rounds fractional seconds
Submitted:	16 May 2008 17:31	Modified:	17 May 2008 21:07
Reporter:	Dan Gunter
Status:	Not a Bug
Category:	Server: DML	Severity:	S2 (Serious)
Version:	5.0.45 MySQL Community Server	OS:	Any
Assigned to:		Target Version:
Tags:	fractional, date

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.

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.

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.

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));