Time() not working with PDO

Question

I have been trying to get this code working for about half an hour and for the life of me, I can't. I've been able to echo the time, but when I try implementing it into my PHP parser, it just returns all 0's. I've tried many different ways, but the code only reflects one way I've tried.

    date_default_timezone_set('America/Los_Angeles');
    $date = date("m/d/Y h:i:s a", time());
    $dbh = new PDO('mysql:host=localhost;dbname=x', 'x', 'x');
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $dbh->prepare("INSERT INTO zzz (x, x1, x2, reply_date) VALUES (:x, :x1, :x2, :date1)");
    $stmt->bindParam(':x', $_POST['yyy']);
    $stmt->bindParam(':x1', $_POST['qqq']);
    $stmt->bindParam(':x2', $_POST['ppp']);
    $stmt->bindParam(':date1', $date);
    $stmt->execute();

score 1 · Accepted Answer · edited May 23 '17 at 12:20

If your reply_datecolumn is a DateTime Column, you have to make sure the date passed is in a format recognizable by your database. Otherwise it will insert 0000-00-00 00:00:00 for the date.

But if you are going to insert the current time anyways, just change your insert from

INSERT INTO zzz (x, x1, x2, reply_date) VALUES (:x, :x1, :x2, :date1)

to

INSERT INTO zzz (x, x1, x2, reply_date) VALUES (:x, :x1, :x2, NOW())

That will take care of it. You can then easily format this date to whatever you need in PHP when you fetch it from the database, e.g.

$dt = new DateTime($row['date']); 
echo $dt->format('m/d/Y h:i:s a')

or the procedural way:

echo date('m/d/Y h:i:s a', strtotime($row['date']);

If you need a timezone with that, check out:

Time() not working with PDO

1 Answers1