How to Convert MySQL Date Strings like 'Apr 15 2012 12:00AM' to UNIX Timestamps?

MySQL: Converting Date Strings to UNIX Timestamps

Converting date strings to UNIX timestamps is a common task in data processing. Here's how to address this need in MySQL.

Problem:

A database returns date strings in the format "Apr 15 2012 12:00AM" including the "AM" suffix. Converting this format using the CONVERT function has not been successful.

Solution:

To convert the given date string to a UNIX timestamp, use the STR_TO_DATE and UNIX_TIMESTAMP functions. The following query demonstrates this:

SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'));

Additional Considerations:

If you also need to change the date format, use the FROM_UNIXTIME function. For example:

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');

The above is the detailed content of How to Convert MySQL Date Strings like 'Apr 15 2012 12:00AM' to UNIX Timestamps?. For more information, please follow other related articles on the PHP Chinese website!