Save PHP date string into MySQL database as timestamp

Question

I am trying to save a datestring in PHP into a MySQL database as timestamp datatype. I found a lot of posts about this, but none of them worked for me. This is what I've tried:

$date =  $_POST['date'];
$timestamp = date("m/d/Y", strtotime($date));
$sql = "insert into sale(service, amount, date, customerid_fk) values('$service', '$amount', '$timestamp', '$id');";

But in the database, I only get:

0000-00-00 00:00:00

The input string from the POST object is 05/30/2013. Thanks a lot for your support!

Solution 1

This might work for you:

$date =  $_POST['date'];
$timestamp = date('Y-m-d H:i:s', strtotime($date));  

 $sql = "insert into sale(service, amount, date, customerid_fk) values('$service', '$amount', '$timestamp', '$id');";

Solution 2

This worked for me:

//timestamp fix
$date = new \DateTime();
$created_at = $date->setTimestamp(intval($timestamp));

Solution 3

We have NOW() function in MySQL.

insert into sale(service, amount, date, customerid_fk)values('$service', '$amount', NOW(), '$id');

Comments

[Pekka]

mySQL expects the Y-m-d format for timestamps. Change the format string accordingly and you'll be good.

[Madthew]

Did you already check this? stackoverflow.com/questions/2501915/…

[Anvesh]

stackoverflow.com/questions/7112982/… is what you need i expect.

[Madthew]

If you want to pass the parameter as a string then convert it into the DB use STR_TO_DATE(yourstring, '%m/%d/%Y')