Search code examples
mysqltimestampscheduler

MySQL Incorrect AT value using current_timestamp with event scheduler

I'm trying to configure a one time event with MySQL scheduler using current_timestamp.

The query is the following one (the second number may vary)

CREATE EVENT event_name ON SCHEDULE AT CURRENT_TIMESTAMP + 30
DO UPDATE test_table SET value = 0 WHERE id = 1;

It sometimes works but I'm often having the following error.

 #1525 - Incorrect AT value: '20211031204269'

It seems that the issue comes from the fact that seconds are directly added to current_timestamp and thus having '68' as seconds value in this example

Is there a way for MySQL to correctly create the timestamp using CURRENT_TIMESTAMP [+ seconds] notation (by incrementing minutes when seconds are greater than 60, same for minutes, hours, days...) or do I have to generate the timestamp by myself ?

Solution

  • thirty what exactly,

    you need to specify the interval you want to add with a timestamp

    interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

    see manual

    CREATE EVENT event_name ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 MINUTE 
DO UPDATE test_table SET value = 0 WHERE id = 1;