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

Count Number of a Specific Day(s) Between Two Dates – Mysql

I have a single line in MySQL table: volunteers

user_id | start_date | end_date
11122   | 2017-04-20 | 2018-02-17

How can I find how many times the 3rd day or 24th day of a month appears? (i.e. 2017-05-03, 2017-06-03, 2017-12-24, 2018-01-24) I’m trying to get to the following count:

Sample Output:

user_id | number_of_third_day | number_of_twenty_fourth_day
11122   |        10           |              10

I look at the documentation online to see if there is a way I can say (pseudo):

SELECT 
day, COUNT(*)
FROM volunteers
WHERE day(between(start_date, end_date)) in (3,24)

I tried to create a calendar table to no avail, but I would try to get the days, GROUP BY day, and COUNT(*) times that day appears in the range

  WITH calendar AS (

  SELECT start_date AS date
  FROM volunteers
  UNION ALL 
  SELECT DATE_ADD(start_date,  INTERVAL 1 DAY) as date
  FROM volunteers
  WHERE DATE_ADD(start_date,  INTERVAL 1 DAY) <= end_date

)

SELECT date FROM calendar;

Thanks for any help!

Tags:

4

Answers


  1. 0

    In last MySQL version you can use recursion:

    -- get list of all dates in interval
with recursive dates(d) as (
    select '2017-04-20'
    union all
    select date_add(d, interval 1 day) from dates where d < '2018-02-17'
) select 
    -- calculate
    sum(day(d) = 10) days_10,
    sum(day(d) = 24) days_24
from dates
-- filter 10 & 24 days
where day(d) = 10 or day(d) = 24;

    https://sqlize.online/sql/mysql80/c00eb7de69d011a85502fa538d64d22c/

    Login or Signup to reply.
  2. 0

    A dynamic approach is.

    but creating the dateranges, takes a lot of time, so you should have a date table to get the dates

    CREATE TABLE table1
    (`user_id` int, `start_date` varchar(10), `end_date` varchar(10))
;
    
INSERT INTO table1
    (`user_id`, `start_date`, `end_date`)
VALUES
    (11122, '2017-04-20', '2018-02-17')
  ,(11123, '2019-04-20', '2020-02-17')
;


    Records: 2  Duplicates: 0  Warnings: 0

    WITH RECURSIVE cte AS (
    SELECT
       user_id,
       `start_date` as date_run ,
       `end_date` 
       FROM table1
    UNION ALL
    SELECT 
        user_id,
        DATE_ADD(cte.date_run, INTERVAL 1 DAY),
        end_date        
    FROM cte 
    WHERE DATE_ADD(date_run, INTERVAL 1 DAY) <= end_date
)SELECT user_id,
  SUM(DAYOFMONTH(date_run) = 3) as day_3th,
  SUM(DAYOFMONTH(date_run) = 24) as day_24th
  FROM cte
GROUP BY user_id
    user_id day_3th day_24th
    11122 10 10
    11123 10 10

    fiddle

    Login or Signup to reply.
  3. 0

    As long as you are looking for days that occur in every month (so not the 29th or beyond), this is just straightforward math. The number of whole calendar months between two dates (exclusive) is:

    timestampdiff(month,start_date,end_date) - (day(start_date) <= day(end_date))

    Then add one if the start month includes the target day and one if the end month includes it:

    timestampdiff(month,start_date,end_date) - (day(start_date) <= day(end_date))
    + (day(start_date) <= 3) + (day(end_date) >= 3)
    Login or Signup to reply.
  4. 0

    This one is more optimized since I generate date range by months not days as other questions, so its faster

    WITH RECURSIVE cte AS
(
  SELECT user_id, DATE_FORMAT(start_date, '%Y-%m-03') as third_day,
   DATE_FORMAT(start_date, '%Y-%m-24') as twenty_fourth_day,
   start_date, end_date
   FROM table1
  UNION ALL
  SELECT user_id, 
   DATE_FORMAT(third_day + INTERVAL 1 MONTH, '%Y-%m-03') as third_day,
   DATE_FORMAT(twenty_fourth_day + INTERVAL 1 MONTH, '%Y-%m-24') as twenty_fourth_day,
   start_date, end_date
  FROM cte
  WHERE third_day + INTERVAL 1 MONTH <= end_date
)
SELECT user_id,
 SUM(CASE WHEN third_day BETWEEN start_date AND end_date THEN 1 ELSE 0 END) AS number_of_third_day,
 SUM(CASE WHEN twenty_fourth_day BETWEEN start_date AND end_date THEN 1 ELSE 0 END) AS number_of_twenty_fourth_day
FROM cte
GROUP BY user_id;

    Demo here

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

Back To Top
Search