CODEWITHSUNDEEP
mysqldatemilliseconds
Piko
Piko

Reputation: 201

Convert Date to Milliseconds in MySQL

I am trying to convert a date in MySQL to milliseconds. This is what I have to get the date: 

DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)

But that returns me like 15/02/2015 and I want to get the milliseconds of that date.

Upvotes: 20

Views: 39817

Answers (1)

Patrick
Patrick

Reputation: 731

Use the UNIX_TIMESTAMP function.

SELECT (UNIX_TIMESTAMP(mydate)*1000) FROM...

UNIX_TIMESTAMP will get you seconds and you need to multiply by 1000 to get milliseconds.

To convert back, use FROM_UNIXTIME() function.

SELECT FROM_UNIXTIME(date_in_milliseconds/1000) FROM ...

Again, you need to divide by 1000 to get it to seconds before using the function.

Upvotes: 47

Related Questions