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!

mySQL expects the Y-m-d format for timestamps. Change the format string accordingly and you'll be good. — Pekka
– Pekka, Commented May 9, 2013 at 8:50
Did you already check this? stackoverflow.com/questions/2501915/… — Madthew
– Madthew, Commented May 9, 2013 at 8:51
stackoverflow.com/questions/7112982/… is what you need i expect. — Anvesh
– Anvesh, Commented May 9, 2013 at 8:52
If you want to pass the parameter as a string then convert it into the DB use STR_TO_DATE(yourstring, '%m/%d/%Y') — Madthew
– Madthew, Commented May 9, 2013 at 8:54

Vijaya Pandey · Accepted Answer · 2013-05-09 09:00:31Z

27

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');";

answered May 9, 2013 at 9:00

Vijaya Pandey

4,2705 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam Deering · Accepted Answer · 2015-05-28 03:53:26Z

1

This worked for me:

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

answered May 28, 2015 at 3:53

Sam Deering

3681 silver badge7 bronze badges

Comments

Karolis Koncevičius · Accepted Answer · 2018-05-05 08:44:09Z

1

We have NOW() function in MySQL.

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

edited May 5, 2018 at 8:44

Karolis Koncevičius

9,7569 gold badges59 silver badges94 bronze badges

answered May 5, 2018 at 6:42

JAYESH

93 bronze badges

Comments

Emanuele Pavanello · Accepted Answer · 2013-05-09 08:51:29Z

0

you need to remove apex for put timestamp as long

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

or use this format for date 'Y-m-d H:i:s'

answered May 9, 2013 at 8:51

Emanuele Pavanello

8831 gold badge11 silver badges26 bronze badges

Comments

user2362083 · Accepted Answer · 2013-05-09 08:53:21Z

0

if you have date field type as DATE or DATE/TIME it will add timestamps like this. you need to make your date field type as varchar and add your timestamps as strings.

if you need to add date in format d/m/y then mysql has different format like y-m-d so before adding you can do like this

date('y-m-d', strtotime('your-date-string'))

answered May 9, 2013 at 8:53

user2362083

337 bronze badges

Comments

Martin Tournoij · Accepted Answer · 2016-08-26 20:24:44Z

0

you should use mysql methods

I got this date from a datebox of easyui

08/25/2016

then

$date = "FROM_UNIXTIME(".strtotime(08/25/2016).")";

INSERT INTO TABLE (col1, col2, dateI) values($a, $X, $date);

edited Aug 26, 2016 at 20:24

Martin Tournoij

28k24 gold badges110 silver badges158 bronze badges

answered Aug 26, 2016 at 19:59

Marco Valencia

1

Comments

Martin Tournoij · Accepted Answer · 2016-08-26 20:25:30Z

0

SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');  -- **1111885200**
SELECT FROM_UNIXTIME(1111885200);              -- **2005-03-27 03:00:00**

$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', UNIX_TIMESTAMP('$timestamp'), '$id');"

edited Aug 26, 2016 at 20:25

Martin Tournoij

28k24 gold badges110 silver badges158 bronze badges

answered Jan 6, 2016 at 8:50

swap

2923 silver badges18 bronze badges

Collectives™ on Stack Overflow

Save PHP date string into MySQL database as timestamp

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related