| Bug #78149 | TIMESTAMPDIFF vs TIMESTAMPADD and DATE_ADD : count months in different way | ||
|---|---|---|---|
| Submitted: | 20 Aug 2015 11:02 | Modified: | 20 Aug 2015 19:06 |
| Reporter: | Sergey Dudchenko | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0/5.1/5.5/5.6/5.7 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
[20 Aug 2015 19:06]
MySQL Verification Team
Thank you for the bug report. C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.97-Win X64 Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.0 > Select TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00'); +------------------------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00') | +------------------------------------------------------------------+ | 5 | +------------------------------------------------------------------+ 1 row in set (0.00 sec) C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.74-Win X64 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.1 > Select TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00'); +------------------------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00') | +------------------------------------------------------------------+ | 5 | +------------------------------------------------------------------+ 1 row in set (0.00 sec) C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.46-log Source distribution PULL: 2015/08/14 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 > Select TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00'); +------------------------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00') | +------------------------------------------------------------------+ | 5 | +------------------------------------------------------------------+ 1 row in set (0.00 sec) C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.27 Source distribution PULL: 2015/08/14 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > Select TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00'); +------------------------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00') | +------------------------------------------------------------------+ | 5 | +------------------------------------------------------------------+ 1 row in set (0.11 sec) C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.9 Source distribution PULL 2015/08/14 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > Select TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00'); +------------------------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00') | +------------------------------------------------------------------+ | 5 | +------------------------------------------------------------------+
Description: Functions TIMESTAMPDIFF and DATE_ADD counts months in different way when in the next month less days than in original date. For example, let's take February 2016: Select TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00'); // Returns 5 but correct value is 6. Select TIMESTAMPADD (MONTH,6,'2015-08-30 11:10:00'); // Returns correct date 2016-02-29 11:10:00 Select DATE_ADD('2015-08-30 11:10:00', INTERVAL 6 MONTH); // Returns correct date 2016-02-29 11:10:00 This inconsistence in approaches doesn't allow developers use them together and they have to create own unnecessary code. How to repeat: Select TIMESTAMPDIFF(MONTH,'2015-08-30 11:10:00','2016-02-29 12:40:00'); // Returns 5 but correct value is 6. Select DATE_ADD('2015-08-30 11:10:00', INTERVAL 6 MONTH); // Returns correct date 2016-02-29 11:10:00 Another example: Select TIMESTAMPDIFF(MONTH,'2015-08-31 11:10:00','2015-09-30 12:40:00'); // Returns 0 but correct value is 1. Select TIMESTAMPADD (MONTH,1,'2015-08-31 11:10:00'); // Returns 2015-09-30 11:10:00 Select DATE_ADD('2015-08-31 11:10:00', INTERVAL 1 MONTH); // Returns 2015-09-30 11:10:00 Suggested fix: Fix TIMESTAMPDIFF function in counting calendar months