Parse custom date format into internal MYSQL format using PHP

Question

Hi,

I've got dates being generated which look lke these:

Sun May 16 23:59:59 GMT 2011

I want to convert them into storable MYSQL form and so that i can read/compare them via php. I am using php. How can i convert the above into something MYSQL will understand?

Answer 1

PHP's internal strtotime function will parse the date into a unix timestamp. From there, you can either just store the timestamp, or use a quick PHP function to turn it into a date object that MySQL can store.

Or you can use MySQL's FROM_UNIXTIME function to get a date format.

Note: strtotime may not bode perfectly with your custom format. You may have to manually edit the string yourself to get a format that works with PHP's parsing function, which you can then follow the steps listed with.

Answer 2

# make an array
$bits = preg_split('/\s/', 'Sun May 16 23:59:59 GMT 2011');

# YYYY-MM-DD HH:mm:SS is mySQL datetime format
$mysql_datetime = date('Y-m-d H:i:s', strtotime("$bits[1] $bits[2] $bits[5] $bits[3]"));
echo $mysql_datetime;

This parses your date format and turns it into proper mysql datetime format.