SQL: Convert bigint type to formatted date

I believe this is a simple question, but I’ve been searched around and got no satisfactory answer.

Basically I have a Bigint object in MYSQL, I want to convert it to date format like 20201004, for example:

1601625689496 -> (20201002)

I’ve tried

to_date(cast(1601625689496 as timestamp))
date(cast(1601625689496 as timestamp))

But neither allow formatting, I hope to get the easiest and fastest conversion.

Answer

Assuming that your number is an epoch timestamp in milliseconds (that is, the number of milliseconds since January 1st, 1970), you can use from_unixtime():

select from_unixtime(1601625689496 / 1000)

This gives you a datetime value. If you want to drop the time component, then:

select date(from_unixtime(1601625689496 / 1000))

Note that 1601625689496 actually maps to 2020-10-02, not 2020-10-04.