MySQL add 12 hours to a time field

Issue

I need to add 12 hours to a MySQL TIME field (not DATETIME) and I’m having trouble.

UPDATE `events` 
SET start_time = DATE_ADD(start_time, INTERVAL 12 HOUR)
WHERE `start_time` < '11:00:00'

returns with no errors but doesn’t change anything, I think because start_time is a TIME field.

UPDATE `events` 
SET start_time = start_time + '12:00:00'
WHERE `start_time` < '11:00:00'

adds 12 seconds.

Solution

Try using ADDTIME instead of DATE_ADD. You could do SET start_time = ADDTIME(start_time, '12:00:00')

Answered By – nybbler

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published