Bug #72761 MySQL 5.6 FROM_UNIXTIME function returns different result than 5.5
Submitted: 27 May 2014 10:00 Modified: 27 May 2014 17:01
Reporter: Mitsuru Oka Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: date, fractional

[27 May 2014 10:00] Mitsuru Oka
Description:
1) In MySQL 5.6, FROM_UNIXTIME function returns fraction part which is not described in "data and time functions" section at the reference manual. MySQL 5.5 does not return.

http://dev.mysql.com/doc/refman/5.6/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 format, depending on whether the function is used in a string or numeric context. 

2) In MySQL 5.6, FROM_UNIXTIME function with format '%s' returns floored value. MySQL 5.5 returns rounded value.

How to repeat:
MySQL 5.5.30:
mysql> select time,time/1000000, time div 1000000,from_unixtime(time/1000000),from_unixtime(time div 1000000),from_unixtime(time/1000000,'%Y-%m-%d %H:%i:%s') from (select 1398561576624099 as time)x\G
*************************** 1. row ***************************
                                           time: 1398561576624099
                                   time/1000000: 1398561576.6241
                               time div 1000000: 1398561576
                    from_unixtime(time/1000000): 2014-04-27 10:19:37
                from_unixtime(time div 1000000): 2014-04-27 10:19:36
from_unixtime(time/1000000,'%Y-%m-%d %H:%i:%s'): 2014-04-27 10:19:37
1 row in set (0.00 sec)

MySQL 5.6.17:
mysql> select time,time/1000000, time div 1000000,from_unixtime(time/1000000),from_unixtime(time div 1000000),from_unixtime(time/1000000,'%Y-%m-%d %H:%i:%s') from (select 1398561576624099 as time)x\G
*************************** 1. row ***************************
                                           time: 1398561576624099
                                   time/1000000: 1398561576.6241
                               time div 1000000: 1398561576
                    from_unixtime(time/1000000): 2014-04-27 10:19:36.6240
                from_unixtime(time div 1000000): 2014-04-27 10:19:36
from_unixtime(time/1000000,'%Y-%m-%d %H:%i:%s'): 2014-04-27 10:19:36
1 row in set (0.00 sec)

Suggested fix:
If you would change the function behavior, please describe that in documentation.
[27 May 2014 17:01] Miguel Solorzano
Thank you for the bug report.