As, i was going through a situtaion where i need to convert long unix timestamp to a format in which milisecond is also included
there i found,
till Mysql 5.6 if you convert long unix timestamp to a date time format using FROM_UNIXTIME
you would not get the milisecond and it will round off to second
You need to take care your self for that
But By 5.7 that function would provide fractional second or milisecond
so what is the matter, let’s have an example
I have a column that has unix timestamp
let’s say the value is 1465818400888
so Before 5.7
if you do like
> SELECT FROM_UNIXTIME(1465818400888/1000);
result would be 2016-06-13 11:46:41 while it should be 2016-06-13 17:16:40.8880
here you can see, .888 is round off to a second.
while that might be very important to maintain orders on basis of time in near to real time application
so there you have to implement a logic to get fractional second
> SELECT CONCAT(FROM_UNIXTIME(SUBSTRING_INDEX(1465818400888/1000,”.”,1)), “.”, SUBSTRING_INDEX(1465818400888/1000,”.”,-1)) AS fractionalSecond;
But In Mysql 5.7
if you do
> SELECT FROM_UNIXTIME(1465818400888/1000);
it would give you the result like 2016-06-13 11:46:40.8880
mysql 5.7 enhance the TIME, DATETIME and TIMESTAMP to store fractional value and it also support the facility to provide fractional seconds precision upto 6 precision
like DATETIME(2), then you can store like 2014-09-08 17:51:04.78