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