Menu
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.
671
To add 2 hours in the current time, we will use the DATE_ADD() function. mysql> select DATE_ADD(now(),interval 2 hour);
Use the ADDTIME() function if you want to select a new datetime by adding a given time to a datetime/timestamp/time value.
We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 10 minutes in the past.
The date and time data types for representing temporal values are DATE , TIME , DATETIME , TIMESTAMP , and YEAR . Each temporal type has a range of valid values, as well as a “zero” value that may be used when you specify an invalid value that MySQL cannot represent.
Try using ADDTIME instead of DATE_ADD. You could do SET start_time = ADDTIME(start_time, '12:00:00')
187
UPDATE `events`
SET start_time = start_time + INTERVAL 12 HOUR
WHERE `start_time` < '11:00:00'
The MySQL functions that accept INTERVAL arguments are mostly unnecessary; you can just add and subtract intervals with + and -.
12
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
