Bug #109941 wrong result of timestampadd
Submitted: 6 Feb 2023 2:40 Modified: 6 Feb 2023 19:20
Reporter: Huaiyu Xu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[6 Feb 2023 2:40] Huaiyu Xu 
Description:
timestampadd(month,1,date '2024-03-30') and select timestampadd(month,1,date '2024-03-31') get the same result.

mysql>  select timestampadd(month,1,date '2024-03-30');
+-----------------------------------------+
| timestampadd(month,1,date '2024-03-30') |
+-----------------------------------------+
| 2024-04-30                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>  select timestampadd(month,1,date '2024-03-31');
+-----------------------------------------+
| timestampadd(month,1,date '2024-03-31') |
+-----------------------------------------+
| 2024-04-30                              |
+-----------------------------------------+
1 row in set (0.00 sec)

How to repeat:
As the description shows.

Suggested fix:
mysql>  select timestampadd(month,1,date '2024-03-30');
+-----------------------------------------+
| timestampadd(month,1,date '2024-03-30') |
+-----------------------------------------+
| 2024-04-30                              |
+-----------------------------------------+
1 row in set (0.00 sec)

tidb> select timestampadd(month,1,date '2024-03-31');
+-----------------------------------------+
| timestampadd(month,1,date '2024-03-31') |
+-----------------------------------------+
| 2024-05-01                              |
+-----------------------------------------+
1 row in set (0.00 sec)
[6 Feb 2023 4:42] MySQL Verification Team 
Hello Huaiyu Xu,

Thank you for the report and test case.

regards,
Umesh
[6 Feb 2023 19:19] Jon Stephens 
This is expected behaviour. Per Development, "...[W]hen
a month interval is added to a date or datetime value:
When the resulting date ends on a day that does not exist
in the respective month, the day is adjusted to be the last
day of the month."

Note that DATE_ADD() behaves in the same fashion.
[6 Feb 2023 19:20] Jon Stephens 
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Fixed in mysqldoc rev 74951. Closed.