Question 294302 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – unexpected behaviour with dates

I am trying to list all the months between two dates in Mysql and ended up with the following:

WITH RECURSIVE dates AS ( 
    SELECT start_date as period, end_date 
    FROM progetti WHERE id = 1 
    UNION ALL 
    SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date FROM dates WHERE period < end_date ) 
SELECT * from dates;

That returns:

period end date
2022-01-01 2022-12-31
2022-02-01 2022-12-31
2022-03-01 2022-12-31
2022-04-01 2022-12-31
2022-05-01 2022-12-31
2022-06-01 2022-12-31
2022-07-01 2022-12-31
2022-08-01 2022-12-31
2022-09-01 2022-12-31
2022-10-01 2022-12-31
2022-11-01 2022-12-31
2022-12-01 2022-12-31
2023-01-01 2022-12-31

Why do I also get the last row? isn’t 2023-01-01 greater than 2022-12-31?
The same happens with any set of dates. I always get one record more.

Tags:

3

Answers


  1. 0

    The last row is a selection from pre-last row where 2022-12-01 is less than 2022-12-31.

    If day component in start_date may differ from 01 then you must use complex query:

    WITH RECURSIVE dates AS ( 
    SELECT start_date, start_date as period, end_date, 1 as rn
    FROM progetti 
    WHERE id = 1 
  UNION ALL 
    SELECT start_date, start_date + INTERVAL rn MONTH, end_date, rn + 1
    FROM dates 
    WHERE period <= end_date - INTERVAL 1 MONTH) 
SELECT period, end_date 
FROM dates;

    This avoids day deviation (for example, if start_date = '2022-01-31 then all next rows will contain 28 in day component of the date).

    https://dbfiddle.uk/eViMoI9c

    Login or Signup to reply.
  2. 0

    When its arrive to period 2022-12-01 the comparaison WHERE period < end_date will be translated to WHERE ‘2022-12-01’ < ‘2022-12-31’ which is true then as result you will get also 2023-01-01 :

    To exclude 2023-01-01 try this :

    WITH RECURSIVE dates AS 
(
    SELECT start_date as period, end_date
    FROM progetti
    WHERE id = 1
    UNION ALL
    SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date
    FROM dates 
    WHERE DATE_ADD(period, INTERVAL 1 MONTH) < end_date
)
SELECT *
FROM dates

    Demo here

    Login or Signup to reply.
  3. 0

    Even though 2023-01-01 is greater than 2022-12-31, the condition period < end_date is still true since 2023-01-01 is considered less than 2022-12-31 for the purpose of the recursive query. This happens because you are comparing the individual months without considering the year.

    WITH RECURSIVE dates AS ( 
    SELECT start_date as period, end_date 
    FROM progetti WHERE id = 1 
    UNION ALL 
    SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date FROM dates WHERE 
    YEAR(period) < YEAR(end_date) OR 
    (YEAR(period) = YEAR(end_date) AND MONTH(period) < MONTH(end_date))
) 
SELECT * from dates;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search