CODEWITHSUNDEEP
mysql
GwydionFR
GwydionFR

Reputation: 787

Timestamp shifting in mySQL

I want to shift timestamp to the next 6h P.M, but I have no clue how to do it in mySQL. Timestamps concerned are range from 7h P.M to 6 A.M, which means it can be the next day after the operation. My actual request is:

UPDATE issues 
SET due_date = due_date - (due_date % 86400) + (18 * 3600) 
WHERE HOUR(due_date) < 6

but it sounds hardcore and it doesn't work for due_dates after 6pm

Upvotes: 0

Views: 57

Answers (1)

Barmar
Barmar

Reputation: 781750

If the date is after 6pm, add another 86400 seconds to go to the next day.

UPDATE issues
SET due_date = IF(HOUR(due_date) < 6,
                  due_date - (due_date % 86400) + (18 * 3600),
                  due_date - (due_date % 86400) + (18 * 3600) + 86400)

Upvotes: 1

Related Questions