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

MySQL get all weekend days between two dates

I’m trying to calculate how many weekend days passed between two dates.

  SELECT DISTINCT
    start_date AS fr_date,
    end_date AS to_date,
    DATE_FORMAT(start_date, '%a') AS wd_fr,
    DATE_FORMAT(end_date, '%a') AS wd_to,
    (

        FLOOR(TIMESTAMPDIFF(DAY, start_date, end_date) / 7) * 2

        + IF(DATE_FORMAT(start_date, '%w') IN (0, 6), 1, 0)
        + IF(DATE_FORMAT(end_date, '%w') IN (0, 6), 1, 0)

        + CASE
            WHEN DATE_FORMAT(start_date, '%w') <= DATE_FORMAT(end_date, '%w')
                 AND DATE_FORMAT(start_date, '%w') IN (0, 6)
            THEN CASE WHEN DATE_FORMAT(end_date, '%w') IN (0, 6) THEN 2 ELSE 1 END
            ELSE 0
        END
    ) AS weekend_days
FROM table
WHERE end_date > start_date
ORDER BY start_date DESC;

I tried using GPT but it didn’t bring any value.
Date format is yyyy-mm-dd. Code sometimes doubles Sundays or ignoring Saturdays and I feel like I got stuck up to this point

Tags:

3

Answers


  1. 0

    I assume you have a table that contains the start and end dates and you want to calculate the weekend days for each row? If that’s the case you could try it like this:

    SELECT DISTINCT
    start_date AS fr_date,
    end_date AS to_date,
    (
        -- Calculate the number of days between start_date and end_date
        TIMESTAMPDIFF(DAY, start_date, end_date)
        
        -- Generate a sequence of dates and count how many fall on a weekend
        - SUM(CASE 
                WHEN DATE_FORMAT(DATE_ADD(start_date, INTERVAL t.n DAY), '%w') IN (0, 6) 
                THEN 0 
                ELSE 1 
              END)
    ) AS weekend_days
FROM 
    (SELECT @row := @row + 1 AS n 
     FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t1, 
          (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t2, 
          (SELECT @row := -1) t3) t
JOIN your_table ON t.n <= TIMESTAMPDIFF(DAY, start_date, end_date)
WHERE end_date > start_date
ORDER BY start_date DESC;

    This query calculates the number of weekend days (Saturdays and Sundays) that occur between two specified dates. It first determines the total number of days between the start_date and end_date using the TIMESTAMPDIFF function.

    Then, for each day in this range, it checks whether the day falls on a weekend by using the DATE_FORMAT function, which returns 0 for Sunday and 6 for Saturday. The query counts how many of these days are weekends and returns the total number of weekend days within the specified date range. This is done dynamically for each row in the table where the end_date is later than the start_date.

    Login or Signup to reply.
  2. 0

    Using the fact that 1900-01-01 is Monday (Unix Epoch). I would try something like this:

    SELECT DISTINCT
    start_date,
    end_date,
    (
       ((datediff(end_date, "1900-01-01") + 1) DIV 7)    -- Saturdays from 1900-01-01 to end_date
     - ((datediff(start_date, "1900-01-01") + 1) DIV 7)  -- Saturdays from 1900-01-01 to start_date
     + (datediff(end_date, "1900-01-01") DIV 7)          -- Sundays from 1900-01-01 to end_date
     - (datediff(start_date, "1900-01-01") DIV 7)        -- Sundays from 1900-01-01 to start_date
    ) as weekend_days
FROM your_table;
    Login or Signup to reply.
  3. 0

    You are very close to your solution. You are missing small things. By looking at your SQL, you are trying to calculate the number of weekend days between two dates.

    Here is the revised code:

    SELECT DISTINCT
  start_date AS fr_date,
  end_date AS to_date,
  (
    FLOOR(TIMESTAMPDIFF(DAY, start_date, end_date) / 7) * 2
    + IF(WEEKDAY(start_date) >= 5, 1, 0)
    + IF(WEEKDAY(end_date) >= 5, 1, 0)
  ) AS weekend_days
FROM table
WHERE end_date > start_date
ORDER BY start_date DESC;

    I removed the DATE_FORMAT functions, as they are not necessary, Instead, I am using WEEKDAY function, which returns the day of the week as an integer (0- Monday, 1 = Tuesday, … and 6 = Sunday)

    I simplified the query by counting the weekend days. I am adding 1 to the count and if it’s Sunday or Saturday will return 5 and 6.

    I also removed the CASE statement, as it is not needed with the new logic.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search