How to MySQL Event Scheduler with dynamic datetime?

Question

Table table_cache is like this:

    id   cache_name     created date
    1    cache one      2016-03-06 01:20:04
    2    cache two      2016-03-06 09:40:34
    3    cache three    2016-03-06 11:40:04

MySQL script to truncate table_cache is like this:

CREATE 
EVENT `deleteEvent`
ON SCHEDULE EVERY 30 MINUTE STARTS '2016-04-06 00:00:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
TRUNCATE table_cache;

It will truncate table_cache every 30 minute star 2016-04-06 00:00:00

But that's not what I want. I want truncate table_cache every 30 minute star field created_date from table table_cache

So, based on the above table:

id=1, truncate on 2016-03-06 01:50:04
id=2, truncate on 2016-03-06 10:10:34
id=3, truncate on 2016-03-06 12:10:04

Whether it can be done?

score 2 · Answer 1 · answered Apr 05 '16 at 19:23

2

TRUNCATE is, by definition, an operation that removes every single row from the table. It isn't what you want here.

What you want is probably something along the lines of

DELETE FROM table_cache
       WHERE created_date < DATE_SUB(NOW(), INTERVAL 30 MINUTE)

which will remove all entries that are older than 30 minutes at the time the query is run.

You will need to decide how often to run this query based on how exact you need the lifetime to be. (For instance, if you run the query on every half hour, a row that is created at 8:01 will only become eligible for deletion at 8:31, so it will persist until the query runs at 9:00, making it actually last 59 minutes instead of 30. Whether this is a problem will depend on your application.)

answered Apr 05 '16 at 19:23

So, for my case, it's not using MySQL Event Scheduler ? – moses toh Apr 05 '16 at 19:59
You could use the event scheduler to run that query periodically, just as you've done in your example. – Apr 05 '16 at 20:10
So, MySQL Event Scheduler can not using dinamyc datetime? – moses toh Apr 05 '16 at 20:17
It's technically possible (by scheduling a separate event to delete each row), but will perform poorly with lots of rows. I wouldn't recommend it. – Apr 05 '16 at 20:28
I had no other choice. So, the complete script : CREATE EVENT `deleteEvent` ON SCHEDULE EVERY 30 MINUTE STARTS '2016-04-06 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM table_cache WHERE created_date < DATE_SUB(NOW(), INTERVAL 30 MINUTE) . Like that? – moses toh Apr 05 '16 at 20:58
You would need to set the event as `ON COMPLETION PRESERVE`. Setting the event as `ON COMPLETION NOT PRESERVE` makes it only run once, then get deleted (making `EVERY 30 MINUTE` pointless). – Apr 05 '16 at 21:13
CREATE EVENT `deleteEvent` ON SCHEDULE EVERY 30 MINUTE STARTS '2016-03-23 00:00:00' ON COMPLETION PRESERVE ENABLE DO DELETE FROM hotel_destination_cache WHERE created_date < DATE_SUB(NOW(), INTERVAL 30 MINUTE) . Like that? – moses toh Apr 05 '16 at 21:30
the query not success – moses toh Apr 06 '16 at 03:16
I need you help. Look here : http://stackoverflow.com/questions/38093418/how-to-find-the-time-elapsed-since-a-date-time-and-what-time-is-it-like-notifica – moses toh Jun 29 '16 at 08:36

How to MySQL Event Scheduler with dynamic datetime?

1 Answers1