How am I minus 2 hours from the date / time field in EVERY record in the table?

Question

I am trying to compensate for a timezone error from PHP. All the times recorded in the test table were two hours ahead. I want to update each record, past two hours from the time that already exists.

I tried:

UPDATE test 
   SET LastModifiedDate = SUBTIME( LastModifiedDate, '02:00:00' ) 

But it just updates all fields with the same value.

Please, help

tthanks

Answer 1

update test set LastModifiedDate = LastModifiedDate - interval 2 hour;

Answer 2

Use the DATE_SUB () function :

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

First check it out to make sure it does what you want:

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

Answer 3

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

-2 . "where" .