Bug #73651 date_sub(date('2014-06-30'), interval 1 month) gives 5/30 instead of 5/31

• Submitted: 20 Aug 2014 3:17
• Modified: 20 Sep 2014 14:12
• Reporter: Max Mir
• Status: No Feedback
• Category: MySQL Server: Parser
• Severity: S2 (Serious)
• Version: 5.5.37-0ubuntu0.14.04.1
• OS: Linux (Ubuntu 14.04.1)
• CPU Architecture: Any
• Tags: date_sub, end of june

[20 Aug 2014 3:17] Max Mir

Description:
select date_sub(date('2014-06-30'), interval 1 month);
should return 2014-05-31
instead, it returns 2014-05-30

How to repeat:
execute this SQL
select date_sub(date('2014-06-30'), interval 1 month);

Suggested fix:
The date should show 2014-05-31

[20 Aug 2014 13:53] MySQL Verification Team

Hello Max,

Thank you for the report.
I checked internally with dev's and this is an expected behavior i.e it reduces the number of months by N months. In your case N = 1 …. and only then adjusts  for a date ……… e.g. if it comes out 30th of June, it will correct it to the previous date and it does not look for the last day in the month.

Also, SQL standard does not cover these functions and you may notice that some of RDBMS(with extensions) may return the value which you are expecting..

If you think this behavior should be documented then we can consider this as doc change request.

Thanks,
Umesh

[20 Aug 2014 14:02] MySQL Verification Team

Also, PostgreSQL returns same as MySQL

// PostgreSQL

test=# select date '2014-06-30' -  interval '1 month' AS DT;
         dt
---------------------
 2014-05-30 00:00:00
(1 row)

test=# select date '2014-06-30' -  interval '2 month' AS DT;
         dt
---------------------
 2014-04-30 00:00:00
(1 row)

test=# select date '2014-06-30' -  interval '3 month' AS DT;
         dt
---------------------
 2014-03-30 00:00:00
(1 row)

test=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit
(1 row)

// MySQL

mysql> select date_sub(date('2014-06-30'), interval 1 month);
+------------------------------------------------+
| date_sub(date('2014-06-30'), interval 1 month) |
+------------------------------------------------+
| 2014-05-30                                     |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub(date('2014-06-30'), interval 2 month);
+------------------------------------------------+
| date_sub(date('2014-06-30'), interval 2 month) |
+------------------------------------------------+
| 2014-04-30                                     |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub(date('2014-06-30'), interval 3 month);
+------------------------------------------------+
| date_sub(date('2014-06-30'), interval 3 month) |
+------------------------------------------------+
| 2014-03-30                                     |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.6.21-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

[20 Aug 2014 14:09] Peter Laursen

I think you wll need to write a smarter statement adding a condition on the month deciding if it should be "INTERVAL 31 DAYS", "INTERVAL 30 DAYS" or "INTERVAL 28 DAYS" (a SELECT CASE construction for instance).

-- Peter
-- not a MySQL/Oracle person.

[21 Sep 2014 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".