Incrementing a datetime in PHP

May 12, 2011 6:25 PM

How can I increment a mySQL datetime in PHP?

I need to be able to increment a mySQL datetime formatted timestamp in my PHP code. In this specific situation, with every iteration of my loop, I need to increment a timestamp by one hour.

So, for example, if it starts as "2011-01-01 00:00:00" then on the next iteration of my loop it needs to read "2011-01-01 01:00:00". What can I add/do to the datetime in each loop to make it one hour larger (and so, when it goes from 23:00:00 to 00:00:00, it goes to the next date, too!)

Any help is appreciated!

posted by ofcourseican to Computers & Internet (9 answers total)

Best answer: Most of PHP's date/time stuff works in terms of Unix timestamps, which are seconds since midnight, January 1, 1970, UTC. You can convert from MySQL's timestamp format to Unix timestamp using the strtotime function, then add one hour's worth of seconds (3600), then convert back using the strftime function.
posted by andrewpendleton at 6:30 PM on May 12, 2011

What andrewpendleton said. Note that both those functions use the server's local timezone setting, but if you're converting back the adjustments should cancel each other out.
posted by neckro23 at 6:35 PM on May 12, 2011

Oh, and you can use the UNIX_TIMESTAMP() function in MySQL to do the conversion to epoch time at the database end if you wish:

SELECT (UNIX_TIMESTAMP(myDate) + 3600) AS futureTime;

...but if you're iterating this through a loop it's probably best to do it in PHP instead. You'd also have to make sure the database server and PHP server are both configured correctly and have the same timezone (or else compensate for the difference).
posted by neckro23 at 6:41 PM on May 12, 2011

Best answer: pass this function the mysql result and it should give you back a mysql-formatted string of that result + 1 hour:

function addOneHour($timestamp)
{
$php_formatted_date = strtotime($timestamp);
$php_formatted_date = $php_formatted_date + 3600;
$mysql_formatted_date = strftime("%Y-%m-%d %H:%m:%s", $php_formatted_date)

return $mysql_formatted_date;
}
posted by lefty lucky cat at 6:55 PM on May 12, 2011

Response by poster: Hmmmm, well, when I try that function, l.l.c, I end up with things like 01:01:2745782946 for the hour:minute:second part. When I try to break it down and do it in one line, I end up getting 1969 as the year, with odd numbers for the hours/minutes/seconds. I'll keep poking at it and see if I can figure out what's going on....
posted by ofcourseican at 7:37 PM on May 12, 2011

Response by poster: This is what I get when I add an hour (Starting datetime is 2011-01-11 00:00:00)

2011-01-11 01:01:1294725600
posted by ofcourseican at 7:40 PM on May 12, 2011

Best answer: Ah! Got it. The function needs to be:

$mysql_formatted_date = strftime("%Y-%m-%d %H:%M:%S", $php_formatted_date)

The lowercase minutes and seconds was what was messing it up. However, that solves my problem beautifully. Thanks!!!
posted by ofcourseican at 7:46 PM on May 12, 2011

Sorry, got that bit confused with the date() function, which would use "Y-m-d H:i:s". Glad it works.
posted by lefty lucky cat at 8:46 PM on May 12, 2011

The above answers are unsafe in the presence of leap seconds, where an hour will have either 3599 or 3601 seconds in it every once in a while.

Also, PHP5 has proper DateTime objects. I strongly recommend you make the effort to use these and the provided abstraction layer wherever possible, as it will save you the pain of dealing with timezones and localization, leap seconds, leap years, and whatever other stupidities human time systems throw in your face.
posted by spitefulcrow at 9:48 PM on May 13, 2011