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

Pascal MARTIN · Accepted Answer · 2011-03-24 11:45:05Z

44

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)

answered Mar 24, 2011 at 11:45

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Niels Over a year ago

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

Alin P. · Accepted Answer · 2011-03-24 11:43:47Z

13

You have the Date and Time functions.

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

or shorter

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

answered Mar 24, 2011 at 11:43

Alin P.

44.5k13 gold badges79 silver badges93 bronze badges

1 Comment

JCarlosR Over a year ago

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

Muhamad Bhaa Asfour · Accepted Answer · 2011-03-24 11:45:07Z

3

there is the addtime() method in mysql

answered Mar 24, 2011 at 11:45

Muhamad Bhaa Asfour

1,0353 gold badges22 silver badges40 bronze badges

Comments

Taryn · Accepted Answer · 2012-02-17 02:09:38Z

1

DELETE 
FROM table 
WHERE datetime_field < DATE_SUB(NOW(), INTERVAL 1 DAY);

edited Feb 17, 2012 at 2:09

Taryn

249k57 gold badges375 silver badges409 bronze badges

answered Mar 24, 2011 at 11:45

Maxime Pacary

23.2k12 gold badges92 silver badges117 bronze badges

Collectives™ on Stack Overflow

mysql adding hours to datetime

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related