Milliseconds in mysql

Question

This page explains how to format the milliseconds http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now

but how to get the actual millisecond value outside of .00000?

I tried these:

select unix_timestamp()+0; select SYSDATE()+0; select date_format(now(), '%f'); select now()+0; 

but none of the topics gives me exact and precise milliseconds

Answer 1

This is a bug from mysql http://bugs.mysql.com/bug.php?id=8523

Answer 2

If you want to get a microsecond from the field, use% f,

 select FROM_UNIXTIME(DATE_COLUMN/1000,'%Y-%M-%d %H:%i:%s %f') from TABLE_NAME; +-------------------------------------------------------+ | FROM_UNIXTIME(CREATETIME/1000,'%Y-%M-%d %H:%i:%s %f') | +-------------------------------------------------------+ | 2016-March-18 16:02:54 342000 | +-------------------------------------------------------+ 

Source: MYSQL DATE_FORMAT