How to Convert a MySQL Date String (e.g., 'Apr 15 2012 12:00AM') to a Unix Timestamp?

MySQL: Converting Date String to Unix Timestamp

Converting a date string to a Unix timestamp can be a useful technique when working with time-sensitive data.

Problem:

A user encounters issues when attempting to convert a date string from the following format to a Unix timestamp:

Apr 15 2012 12:00AM

The date string obtained from the database includes the AM suffix, and the user's previous attempts using CONVERT functions have failed.

Solution:

To successfully convert the date string to a Unix timestamp, you can use a combination of MySQL functions:

1. STR_TO_DATE: Converts the string to a DATETIME object, with the format specification '%M %d %Y %h:%i%p'.
2. UNIX_TIMESTAMP: Converts the DATETIME object to a Unix timestamp.

The following code demonstrates the correct approach:

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

Additionally, if you need to change the date format, you can use the FROM_UNIXTIME function:

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

This will convert the date to the format mm-dd-yyyy hh:mm:ss.

Documentation:

For more information on these functions, refer to the MySQL documentation for:

• [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)
• [STR_TO_DATE](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date)

The above is the detailed content of How to Convert a MySQL Date String (e.g., 'Apr 15 2012 12:00AM') to a Unix Timestamp?. For more information, please follow other related articles on the PHP Chinese website!