MySQL convert date string to Unix timestamp

mysql datetime timestamp unix-timestamp
redcoder picture redcoder · Jun 21, 2012 · Viewed 332.7k times · Source

How do I convert the following format to unix timestamp?

Apr 15 2012 12:00AM

The format I get from DB seems to have AM at the end. I've tried using the following but it did not work:

CONVERT(DATETIME, Sales.SalesDate, 103) AS DTSALESDATE,  
CONVERT(TIMESTAMP, Sales.SalesDate, 103) AS TSSALESDATE

where Sales.SalesDate value is Apr 15 2012 12:00AM

Answer

Query Master picture Query Master · Jun 21, 2012

Here's an example of how to convert DATETIME to UNIX timestamp:
SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))

Here's an example of how to change date format:
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p')

Documentation: UNIX_TIMESTAMP, FROM_UNIXTIME

Related questions

How to Insert custom date into mysql timestamp field?

I have tried to insert date and time string formatted into mysql timestamp field by using following two methods but both shows me 0000-00-00 00:00:00 INSERT INTO test VALUES ( UNIX_TIMESTAMP('2013-08-05 18:19:03') ) INSERT INTO test VALUES ( UNIX_…

Read More
Should I use the datetime or timestamp data type in MySQL?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)? I'm working with PHP on the server side.

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More