How to add 4 hours to time in PHP/MySQL

Question

I'm working on a blog migration from a custom built blog to Wordpress. One of the fields that Wordpress is looking for in the database is a date/time stamp set to GMT, which is 4 hours ahead of our time. So I basically need to take our date/time stamp (in YYYY-MM-DD HH:MM:SS format), and add four hours to it. I was looking at the MySQL command "ADDTIME", but I think that only works on selects, not on inserts.

I had worked up a script that exploded the date in to parts, and added 4 hours to the time, but the ensuing logic that would be required to check for when 4 hours pushes in to the next day/month/year seemed a little excessive.

Answer 1

date($format, strtotime("$date + 4 hours"));

Answer 2

What about:

date( "Y-m-d H:i:s", strtotime( "2009-08-09 23:44:22" )+4*60*60 )

or even

date( "Y-m-d H:i:s", strtotime( "2009-08-09 23:44:22 + 4 hours" ) )

Might need a bit of error checking, but should solve your problem.

Answer 3

There's nothing that prevents ADDTIME() from being used in an INSERT OR UPDATE, but DATE_ADD() is probably what will work best:

INSERT INTO table_name
SET my_datetime = DATE_ADD('2009-11-01 19:30:00', INTERVAL 4 HOURS),
...other insert columns here... ;

Answer 4

Or better in SQL

DATE(DATE_ADD(`Table`.`Column`, INTERVAL 4 HOURS))

Answer 5

It looks like the TZ of your database is set to GMT (UTC), which is as it should be. You need to convert your local date into GMT when adding to the database.

from the MySQL 5.1 Reference Manual:

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() converts a datetime value dt from time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones may be specified as described in Section 5.10.8, “MySQL Server Time Zone Support”. This function returns NULL if the arguments are invalid.

If the value falls out of the supported range of the TIMESTAMP type when converted fom from_tz to UTC, no conversion occurs. The TIMESTAMP range is described in Section 11.1.2, “Overview of Date and Time Types”.

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

Answer 6

For all the answers using DATE_ADD, the correct syntax is "4 HOUR" not "4 HOURS" (at least in the current version of MySQL):

UPDATE table SET field = DATE_ADD(field, INTERVAL 4 HOUR);

Answer 7

i used this method :P

$format = Y-m-d; //you can use any format if you want
$date = $row['date']; // from mysql_fetch_array
$date2 = date($format, strtotime("$date + 4 week"));
echo $date;