adding time and date to datetime in mysql using php?

Question

I'm trying to add date and time to a datetime field in mysql database using PHP.

My datetime in mysql database is stored like this:

2015-01-20 05:10:17

I tried to add 5 days to that field like this:

$Date = date();
$end_date = date("Y, n, j, G, i, s", strtotime($Date. ' + 5 days'));

UPDATE myTable SET end_date='$end_date' WHERE randKey='$id'

but when I look at the database for updates I see the end_date field like this:

0000-00-00 00:00:00

which means it has been updated but everything has been set to 0 for some reason!

could someone please advise on this issue?

Thanks in advance.

vaso123 · Accepted Answer · 2015-01-20 15:01:46Z

2

Check this:

$end_date = date("Y-m-d H:i:s", strtotime(date("Y-m-d H:i:s"). ' + 5 days'));

MySql store the datetime in YYYY-MM-DD HH:MM:SS format.

answered Jan 20, 2015 at 15:01

vaso123

12.4k4 gold badges37 silver badges68 bronze badges

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

1 Comment

Alec Deitloff Over a year ago

As far as I am aware, the real runtime difference between your solution and mine is that your solution includes a single extra string concatenation, which for something that is probably not high-throughput production code, is of trivial concern. I'd say that either solution should work pretty much identically.

Alec Deitloff · Accepted Answer · 2015-01-20 15:02:31Z

2

Instead of trying to add the five days to the current date in one string, using the two parameters to strtotime would be much easier, as so:

$Date = date();
$end_date = date("Y, n, j, G, i, s", strtotime('+5 days', $Date));

The second parameter specifies what the "current" time is; that is, where to base the +5 days off of (start position). When unspecified, it defaults to time().

answered Jan 20, 2015 at 15:02

Alec Deitloff

4474 silver badges12 bronze badges

Comments

Fenistil · Accepted Answer · 2015-01-20 15:09:44Z

1

First, the code you provided doesn't add 5 days to the value of the field, but five days to today's date. See, if your field contains 2015-01-01 and as you wrote, you would like to add 5 days to this field, you shouldn't add five days in PHP to date() because it will be 2015-01-25 instead of 2015-01-06. So if you want to add 5 days to the field value use this:

UPDATE myTable SET end_date=DATE_ADD(end_date, INTERVAL 5 DAY) WHERE randKey='$id';

But if you want to add five days after today, use this:

UPDATE myTable SET end_date=DATE_ADD(CURDATE(), INTERVAL 5 DAY) WHERE randKey='$id';

If you care for the time also, not just the date, use NOW() instead of CURDATE().

I hope it helps.

edited Jan 20, 2015 at 15:09

answered Jan 20, 2015 at 15:03

Fenistil

3,8111 gold badge29 silver badges33 bronze badges

Collectives™ on Stack Overflow

adding time and date to datetime in mysql using php?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related