MySQL Event Insert ID from another table

Question

I would like to create an event that when a lend_date column has passed exactly 15 days, it would execute an INSERT query.

It would get the ID of that row and userid, and insert it to another table.

For example:

id |  userid  |    lend_date
---+----------+----------------------
1  |    1     |  2015-09-24 15:58:48
2  |    1     |  2015-09-21 08:22:48

And right now, it is exactly 2015-10-06 08:23:41. So the event should get the ID of the second row, which is 2, and insert it to another table.

What should my event query look like?

The event type is RECURRING. But I'm also not sure if I should execute it every hour or everyday. What would be the best recommendation for it?

And is this a better way than using Task Scheduler?

The other table that I wanted to insert the fetched ID is notification_table, where it will notify the user that he/she has an overdue date.

notification_table looks like this:

id |  userid  |  notificationid  |   notificationdate   |
---+----------+------------------+----------------------+
1  |    1     |        1         |  2015-09-24 15:58:48 |
2  |    1     |        1         |  2015-09-21 08:22:48 | 

I'm looking at this query:

INSERT INTO notification_table (id, userid, notificationid, notificationdate) 
        SELECT id, userid, 1, NOW() 
        FROM first_table
        WHERE lend_date + INTERVAL 15 DAY = NOW();

Answer 1

Seeing the words exactly, event, and datetime in the same sentence makes me cringe. Why? For one thing, it's hard to get one datetime value to exactly match another. For another thing, events often run slightly after the scheduled time, especially on a busy database server. It takes them a little time to start up.

If you need the id values from a table where the records are more than 15 days old, the most time-precise way to get them is with a query or view.

 CREATE OR REPLACE VIEW fifteen 
     AS SELECT id
          FROM table
         WHERE `datetime` < NOW() - INTERVAL 15 DAY

You can, of course, write an event to copy the ids to a new table. You'll have to go to some trouble to make sure you don't hit the same id values more than once, by using this sort of query in the event.

   INSERT INTO newtable (id)
        SELECT id
          FROM table
         WHERE `datetime` < NOW() - INTERVAL 15 DAY
           AND id NOT IN (SELECT id FROM newtable) 

How often should you run the repeating event? That depends entirely on how quickly the id values need to make it into the new table after they turn fifteen days old. If your application requires it to be less than a minute, you really should go with the view rather than the event. Anything more than a minute of allowable delay will let you use a repeating event at that frequency.