How to delete rows which have a field greater than 24 hours

Question

I have a table which contains 2 user names and a date - I need a way of processing the table so that when the date stored is greater than 24 hours / 1 day - then it is removed.

I am thinking that my java application could have a thread that could poll and do this, alternatively - is there a solution that could be crafted on the database side? For example, a stored procedure?

Thanks

Start by having a look at [this question](http://stackoverflow.com/questions/13448340/sql-greater-than-date) and [Datetime equal or greater than today in MySQL](http://stackoverflow.com/questions/5182275/datetime-equal-or-greater-than-today-in-mysql) — MadProgrammer, Aug 01 '14 at 07:30
Maybe with a trigger: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html — PeterMmm, Aug 01 '14 at 07:31

Jean Logeart · Accepted Answer · 2014-08-01T09:02:22.453

3

You can use Mysql Event Scheduler to periodically perform an action on the database side.

You can also do it programmatically in java using a ScheduledThreadPoolExecutor:

ScheduledExecutorService ses = Executors.newSingleThreadScheduledExecutor();
// execute every 15 seconds
ses.scheduleAtFixedRate(new Runnable() {
    @Override
    public void run() {
        // execute query to delete the proper data
    }
}, 0, 15, TimeUnit.SECONDS);

edited Aug 01 '14 at 09:02

answered Aug 01 '14 at 07:39

Jean Logeart

52,687
11
83
118

which would you suggest is the best approach - ideally i think thje database handling it but i would like to know if this is maybe not ideal? – Biscuit128 Aug 01 '14 at 08:03
It depends. If this value should really not be in the db, then the db should handle it. If the role of the program is to clean the database while the database can well contain the data to delete, then the probram should do it. If the database should keep the data to keep track of the past insertions and your programs reading the database should just select the data they need, then nobody should delete anything. – Jean Logeart Aug 01 '14 at 08:17

MikeWu · Answer 2 · 2014-08-01T07:37:13.293

1

To run stuff periodically in MySql try Event Scheduler

Sample Evnet definition from MySql docs:

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

edited Aug 01 '14 at 07:37

answered Aug 01 '14 at 07:31

MikeWu

3,042
2
19
27

score 0 · Answer 3 · answered Aug 01 '14 at 07:30

0

You could just do something like:

db.query("DELETE FROM yourtable WHERE TIMESTAMPDIFF(HOUR, NOW(), yourdate) > 24");

and run that once a day.

answered Aug 01 '14 at 07:30

David Xu

5,555
3
28
50

What's `db`? What's `query`? – MadProgrammer Aug 01 '14 at 07:31
Is that a serious question? – David Xu Aug 01 '14 at 07:33
@DavidXu Yes it is. In java there is no standard data type for database connections which do have a `query` method AFAIK. So you should mention what you are using: What is `db`? What's its type? – BackSlash Aug 01 '14 at 07:34
Yes, yes it yes. If the OP is unfamiliar with Java and JDBC, this is a very misleading answer...the query might be alright, and based on the OP's actually question it might answer 1 of the OP's 4 questions... – MadProgrammer Aug 01 '14 at 07:36

How to delete rows which have a field greater than 24 hours

3 Answers3