Php proper time conversion for MySQL

Question

Getting time as following in json result from Google API:

2014-08-25T20:02:24.000Z

how to convert and prepare it for MySQL datetime field?

Answer 1

$dateTime=new DateTime('2014-08-25T20:02:24.000Z');
echo $dateTime->format('Y-m-d H:i:s') ;

outputs as

2014-08-25 20:02:24 mysql DATETIME format

Answer 2

As a simple ISO 8601 representation it is almost what MySQL understands. The only thing that is problematic issue is the "Z" as MySQL does not support time-zones nor does it ignore "Z".

Thus simply touch up the format a bit (such is not a general solution for all dates but it does present a valid solution for this problem, when the date is not used or checked pre-SQL);

$iso = "2014-08-25T20:02:24.000Z";      // literal JSON text
$iso = preg_replace("/Z$/", "", $iso);  // -> "2014-08-25T20:02:24.000"

And insert it into MySQL as normal (with placeholders) like a string;

$msqli_stmt->bind_param("s", $iso);
$pdo_stmt->bindValue(":dt", $iso);

Consider this SQL example;

create table x (y datetime)
insert into x (y) values ('2014-08-25T20:02:24.000')
select * from x
--> August, 25 2014 20:02:24+0000

Answer 3

This is a ISO 8601 date. You can convert it directly to a DateTime object with:

$date = new DateTime('2014-08-25T20:02:24.000Z');

Or to a timestamp to be used with the classic PHP time functions with strtotime():

$time = strtotime('2014-08-25T20:02:24.000Z');

Once you have that simply convert it to the format MySQL uses, Y-m-d H:i:s, by using one of those:

$to_mysql = $date->format('Y-m-d H:i:s');
$to_mysql = date('Y-m-d H:i:s', $time);