How do I minus 2 hours from a date/time field in EVERY record in a table?

I'm trying to offset a timezone error from PHP. All times recorded in table 'test' was ahead by two hours. What I want is to update each record by minusing two hours from the time that is already there.

I tried:

UPDATE test 
   SET 开发者_开发技巧LastModifiedDate = SUBTIME( LastModifiedDate, '02:00:00' ) 

But this just updates all fields with the same value.

Please assist

tthanks

update test set LastModifiedDate = LastModifiedDate - interval 2 hour;

Use the DATE_SUB() function:

UPDATE test SET LastModifiedDate = DATE_SUB(LastModifiedDate, INTERVAL 2 HOUR)

Test it first to be certain it's doing what you want:

SELECT LastModifiedDate, DATE_SUB(LastModifiedDate, INTERVAL 2 HOUR) FROM test;

update test set LastModifiedDate = adddate(LastModifiedDate, interval -2 hour);

this will modify all your dates to -2 hour. you can narrow down the result in "where" section of the query by targeting specific rows.