My MySQL version is 5.6; it has a FROM_UNIXTIME function. But it works for timestamps with 10 digits: 1447430881.
mysql> SELECT FROM_UNIXTIME(1447430881);
-> '2015-11-13 10:08:01'
Now I want to get the date from a milliseconds timestamp (e.g. 1556948248000), so using FROM_UNIXTIME I got NULL.
mysql> SELECT FROM_UNIXTIME(1556948248000);
-> NULL
Isn't there a FROM_UNIXMILLITIME function?
Universal solution (for datetime >= '2001-09-09 04:46:40' - see comments).
SELECT FROM_UNIXTIME(@time * POWER(10, 9 - FLOOR(LOG10(@time))));
@time variable contains unix timestamp (any accuracy). For example, when it is 1556948248123, it is normalized to 1556948248.123 by the FROM_UNIXTIME's argument expression, and the result will be 2015-11-13 19:08:01.123000. The same result will be obtained when the variable is 1556948248.123 or, for example, 1.556948248123.
This variable is a placeholder - it can be fieldname or number literal.
You may create your own UDF if needed:
CREATE FUNCTION my_from_unixtime(arg DECIMAL(65, 30))
RETURNS DATETIME(6)
DETERMINISTIC
NO SQL
RETURN FROM_UNIXTIME(arg * POWER(10, 9 - FLOOR(LOG10(arg))));
'2001-09-09 04:46:40'. But no way to distinguish does the timestamp is too ancient or it is so "over-normalized".
– Akina
Jun 3 '19 at 8:15
Just tested with MySQL version 5.6
5.6.35-80.0-log
Adding milliseconds as the fractional part will work just fine
SELECT FROM_UNIXTIME(1447430881.123);
Will result in
2015-11-13 16:08:01.123
Hope this helps
