MySQL epoch time conversion with microseconds

Question

I have time column stored with epoch timestamp values. I am trying to convert this in readable format as following:

YYYYMMDD HH:MM:SS.uuuuuu

Where last .uuuuuu are microseconds of time stamps. I can display YYYYMMDD HH:MM:SS with this:

SELECT FROM_UNIXTIME(1618096657, '%Y%m%d %H:%i:%s') AS Result;

But as soon as I try to add microseconds using %f flag, it is not working.

SELECT FROM_UNIXTIME(1618096657861406, '%Y%m%d %H:%i:%s.%f') AS Result;

What am I doing wrong here?

Thank you!

Answer 1

The value of the first argument is in seconds, so you need to divide by 1,000,000:

SELECT FROM_UNIXTIME(1618096657861406 / 1000000.0, '%Y%m%d %H:%i:%s.%f') AS Result;

Here is a db<>fiddle.