CODEWITHSUNDEEP
mysqlevents
rascio
rascio

Reputation: 9279

Mysql doesn't create EVENT with end date after 2038-01-19

I was trying to create an event in mysql, and seems that if the end date is more than 20 years from now it fail to create it.

start today, finish tomorrow works:

mysql> CREATE EVENT test
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2020-07-24 13:30:00'
    -> ENDS '2020-07-25 13:30:00'
    -> DO 
    -> SELECT 1 FROM DUAL;
Query OK, 0 rows affected (0.00 sec)

start today finish in 50 years, doesn't work:

mysql> CREATE EVENT test
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2020-07-24 13:30:00'
    -> ENDS '2070-07-25 13:30:00'
    -> DO 
    -> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS

mysql> -- also with different time format

mysql> CREATE EVENT test
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2020-07-24 13:30:00'
    -> ENDS CURRENT_TIMESTAMP + INTERVAL 50 YEAR
    -> DO 
    -> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS

in 20 years neither work:

mysql> CREATE EVENT test
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2020-07-24 13:30:00'
    -> ENDS '2040-07-25 13:30:00'
    -> DO 
    -> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS

mysql> CREATE EVENT test
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2020-07-24 13:30:00'
    -> ENDS CURRENT_TIMESTAMP + INTERVAL 20 YEAR
    -> DO 
    -> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS

15 years is acceptable:

mysql> CREATE EVENT test
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2020-07-24 13:30:00'
    -> ENDS CURRENT_TIMESTAMP + INTERVAL 15 YEAR
    -> DO 
    -> SELECT 1 FROM DUAL;
Query OK, 0 rows affected (0.01 sec)

Does anyone knows why is this happening?

Upvotes: 2

Views: 356

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

MySQL event timestamps use the 32-bit UNIX timestamp format.

https://dev.mysql.com/doc/refman/8.0/en/datetime.html says:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

This limitation is also identified in this bug: https://bugs.mysql.com/bug.php?id=16396

Upvotes: 1

Related Questions