0

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?

1
  • 1
    Add table's DDL.
    – Akina
    May 7, 2020 at 19:48

2 Answers 2

0

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)

1
  • 1
    Agree with all the good points here but also see a couple issues: 1. "Older than today" should be < CURDATE() rather than < CURDATE() - INTERVAL 1 DAY (which means "older than yesterday"). 2. The check_in column is said to be in the "timestamp format", by which I'm assuming they meant the UNIX timestamp format, based on the example further down the text ("midnight oriented ex: 1588809600"). So the solution probably requires one additional step: WHERE check_in < UNIX_TIMESTAMP(CURDATE()).
    – Andriy M
    Feb 27 at 12:22
0

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
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.