mysqldatetimemariadbtimeslots

How to get timeslots based on start date time and end date time?

I need help in preparing query which will provide me 30 minutes slots based on the start date time and end date time.

For e.g.

In my query in between clause i will provide start date as 2024-11-13 00:00:00 and end date as 2024-11-13 02:00:00.

The query result should return as :

****starttime   endtime****

2018-03-12 00:00:00 2018-03-12 00:30:00
2018-03-12 00:30:00 2018-03-12 01:00:00
2018-03-12 01:00:00 2018-03-12 01:30:00
2018-03-12 01:30:00 2018-03-12 02:00:00
Solution

  • You can try this approach:

    WITH RECURSIVE time_slots AS (
    -- Anchor member: Starting point of the time slots (provided start date)
    SELECT 
        CAST('2024-11-13 00:00:00' AS DATETIME) AS starttime,
        DATE_ADD(CAST('2024-11-13 00:00:00' AS DATETIME), INTERVAL 30 MINUTE) AS endtime
    UNION ALL
    -- Recursive member: Add 30 minutes to each previous slot
    SELECT 
        DATE_ADD(starttime, INTERVAL 30 MINUTE),
        DATE_ADD(endtime, INTERVAL 30 MINUTE)
    FROM 
        time_slots
    -- Stop recursion if the endtime exceeds the provided end date
    WHERE 
        endtime < '2024-11-13 02:00:00'
)
-- Select the generated time slots
SELECT starttime, endtime
FROM time_slots
ORDER BY starttime;

    ref: https://sqlize.online/sql/mariadb115/ad0e87fbc2b8c8a01615acec7595a0f7/