mysql adding hours to datetime

Question

In MYSQL DB I need to check if a "datetime" field is more than 24hours (or whatever) ago in which case delete the row.

How to add hours to datetime in mysql?

thanks

Luca

Solution 1

What about something like this :

delete
from your_table
where your_field <= date_sub(now(), INTERVAL 1 day)

With :

• now() : the current date time
• date_sub() to substract 1 day to that date

Or, if you want o use 24 hours instead of 1 day :

delete
from your_table
where your_field <= date_sub(now(), INTERVAL 24 hour)

Solution 2

You have the Date and Time functions.

WHERE `yourDate` < DATE_SUB(NOW(),INTERVAL 1 DAY)

or shorter

WHERE `yourDate` < NOW() - INTERVAL 1 DAY

Solution 3

there is the addtime() method in mysql

Comments

[Niels]

Shouldnt this be date_add, because your not using -1 day? date_sub does the minus, date_add does the plus.

[JCarlosR]

Thanks. This is surprisingly working: SELECT '2018-05-31' + INTERVAL 1 DAY;