How to convert iso-8601 timestamp to MySQL DATETIME

Question

I've searched for this conversion and have found the opposite.

I'm pulling facebook page feeds using the graph api and the date format it gives is iso-8601.

I need to convert that to mysql timestamp to add it to my database. Most search results provide mysql to iso.

Can I get a direct conversion or do I have to convert to unix then mysql or something like that.

Answer 1

Use the mysql function STR_TO_DATE(str,format) in your update statement:

 UPDATE [table] SET [field] = STR_TO_DATE([your input date], '%Y-%m-%dT%H:%i:%s+0000');

The STR_TO_DATE function takes 2 parameters 1)your date string and 2)the format for the output which can be formatted in the same way as mysql DATE_FORMAT() See: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format