Mysql query - Event to delete rows by its value

Question

need help here

I want a query to delete all rows in which column value is older than today. One of the columns is 'check_in' (values in timestamp format) and I want the query to identify the row that has values where the timestamp is older than today (midnight oriented ex: 1588809600 ) and delete them.

Try this but select all rows :

DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 1 DAY)

Maybe something like this but it give me sintax erros :

DELETE FROM events WHERE check_in = timestamp < (NOW() - INTERVAL 1 DAY)

Basically, i want to create an event that every day delete rows where check_in value in older than today.

Can you help?

Answer 1

To fix the syntax error:

DELETE FROM events WHERE check_in < (NOW() - INTERVAL 1 DAY)

That won't land on midnight. Instead change to:

DELETE FROM events WHERE check_in < CURDATE() - INTERVAL 1 DAY

Give writing the EVENT a try.

Be sure to do (once):

SET event_scheduler = ON;

(See Comments below)

Answer 2

Basically, i want to create an event that every day delete rows where check_in value in older than today.

Using MySQL 8.0.34:

CREATE TABLE ts (
    c1 INT AUTO_INCREMENT PRIMARY KEY,
    c2 TIMESTAMP NOT NULL
);

Assume that c2 is the check_in column. Some sample data (and today is 2023-11-30):

Some data:

+----+---------------------+
| c1 | c2                  |
+----+---------------------+
|  1 | 2023-11-30 11:28:57 |
|  2 | 2023-11-28 20:30:00 |
|  3 | 2023-11-29 23:59:59 |
|  4 | 2023-11-30 00:00:01 |
+----+---------------------+

The query:

SELECT * FROM ts WHERE c2 < TIMESTAMP(DATE(CURRENT_TIMESTAMP));

+----+---------------------+
| c1 | c2                  |
+----+---------------------+
|  2 | 2023-11-28 20:30:00 |
|  3 | 2023-11-29 23:59:59 |
+----+---------------------+

The event:

delimiter |

CREATE EVENT delete_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Deletes rows each day where c2 column value is older than today'
    DO
      BEGIN
        DELETE FROM ts WHERE c2 < TIMESTAMP(DATE(CURRENT_TIMESTAMP));
      END |

delimiter ;

Verify the event creation:

SHOW EVENTS\G

*************************** 1. row ***************************
                  Db: test_db
                Name: delete_daily
             Definer: admin_1@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: DAY
              Starts: 2023-11-30 12:02:08
                Ends: NULL
              Status: ENABLED
...