CODEWITHSUNDEEP
mysqlsqlstringdatetime
MJeremy
MJeremy

Reputation: 1250

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.

Upvotes: 0

Views: 1607

Answers (1)

GMB
GMB

Reputation: 222462

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.

Upvotes: 1

Related Questions