Query data in time interval that spans two days in mysql-CodePudding

I need to count data for a given <week | month | custom> interval grouped by a given time schedule that possibly spans 2 days. The chunks depends on customers working schedules.

Possible cases (for one month interval) :

all data from June 01, 2022 to July 01, 2022, each day between 08:00 pm and 04:00 am (overnight)
all data from June 01, 2022 to June 30, 2022, each day between 04:00 am and 08:00 pm
all data from June 01, 2022 to June 30, 2022, each day between 00:00 am and 23:59 pm

Here's what I came up with:

WITH RECURSIVE seq AS (
    SELECT
        0 AS value
    UNION ALL
    SELECT
        value   1
    FROM
        seq
    WHERE
        value < 29
),
period AS (
    SELECT
        '2022-06-01 20:00'   INTERVAL (value * 24 * 60) MINUTE AS start,
        '2022-06-01 20:00'   INTERVAL (value * 24 * 60) MINUTE   INTERVAL (8* 60) MINUTE AS end
    FROM seq
    ORDER BY value DESC
)
SELECT *
FROM (
    SELECT
    DATE(sd.timestamp - INTERVAL(LEAST(20, 4)) HOUR) as date,  
    SUM(...) as count,
FROM sensor_data sd
        WHERE sd.timestamp BETWEEN '2022-06-01 20:00' AND '2022-07-01 04:00'
            AND HOUR(sd.timestamp) >= 20 AND HOUR(sd.timestamp) < 4
GROUP BY 
    date
) main_data
INNER JOIN period ON DATE(period.start) = date

Unfortunately in doesn't work for the first case (spans two days). Any ideas?

CodePudding user response：

WITH RECURSIVE
cte AS (
    SELECT datetime_from range_from, 
           datetime_from   INTERVAL range_length HOUR range_till
    UNION ALL
    SELECT range_from   INTERVAL 1 DAY,
           range_till   INTERVAL 1 DAY
    FROM cte
    WHERE range_till < datetime_till
)
SELECT range_from, range_till, COUNT(*) rows_amount
FROM cte
LEFT JOIN test ON test.dt BETWEEN range_from AND range_till
GROUP BY 1, 2;

DEMO with some explanations.