MySQL – Get Time from Unix Milliseconds Timestamp

MySQLtimestamptype conversion

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?

Best Answer

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))));
Related Question