0

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?

1

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))));
  • Not quite universal. It fails before 2001-09-08 18:46:40. – Rick James Jun 1 at 2:51
  • @RickJames Yes, it fails, when timestamp is less than '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 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.