Search code examples
mysqldatetimetimestampunix-timestamp

MySQL convert date string to Unix timestamp

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
Solution

  • 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