Convert Date to Milliseconds in MySQL

mysql date milliseconds
Piko picture Piko · Feb 17, 2015 · Viewed 28.4k times · Source

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.

Answer

Patrick picture Patrick · Jun 17, 2015

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.

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More
how to convert a string to date in mysql?

I have a string column which acts as a date and I want to select it as a date. Is it possible? My sample data format would be; month/day/year -> 12/31/2011

Read More