How to Convert MySQL Date Strings with AM/PM to Unix Timestamps?

Converting MySQL Date Strings to Unix Timestamps

When retrieving dates from a MySQL database, you may encounter issues with converting them to timestamps. This can be particularly challenging when the date strings include an AM or PM designation. Here's how you can resolve this:

Converting Date Strings to Timestamps

To convert a DATETIME value to a Unix timestamp, use the following format:

UNIX_TIMESTAMP(STR_TO_DATE('your_date_string', '%M %d %Y %h:%i%p'))

In your case, to convert "Apr 15 2012 12:00AM" to a Unix timestamp:

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

Changing Date Format

If you need to change the date format, you can use the FROM_UNIXTIME function along with the appropriate format string. For instance, to convert the Unix timestamp to "mm-dd-yyyy hh:mm:ss" 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

For more information, refer to the MySQL documentation on UNIX_TIMESTAMP and FROM_UNIXTIME functions:

• UNIX_TIMESTAMP: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp
• FROM_UNIXTIME: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime

The above is the detailed content of How to Convert MySQL Date Strings with AM/PM to Unix Timestamps?. For more information, please follow other related articles on the PHP Chinese website!