Category: Database

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

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;

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

for more http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html