CODEWITHSUNDEEP
mysqlsql
sqluf
sqluf

Reputation: 61

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

Upvotes: 6

Views: 9210

Answers (3)

Dhwanit
Dhwanit

Reputation: 153

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.

Upvotes: 3

Nicola Cossu
Nicola Cossu

Reputation: 56397

update test set LastModifiedDate = LastModifiedDate - interval 2 hour;

Upvotes: 11

Michael Berkowski
Michael Berkowski

Reputation: 270757

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;

Upvotes: 9

Related Questions