如何基于起止时间生成30分钟时间槽的SQL查询？

MySQL 8.0+ 版本（递归CTE实现） #

直接替换语句中的起始时间和结束时间即可生成对应时间槽：

WITH RECURSIVE time_slots AS (
    SELECT 
        STR_TO_DATE('2024-11-13 00:00:00', '%Y-%m-%d %H:%i:%s') AS starttime,
        DATE_ADD(STR_TO_DATE('2024-11-13 00:00:00', '%Y-%m-%d %H:%i:%s'), INTERVAL 30 MINUTE) AS endtime
    UNION ALL
    SELECT 
        endtime AS starttime,
        DATE_ADD(endtime, INTERVAL 30 MINUTE) AS endtime
    FROM time_slots
    WHERE endtime < STR_TO_DATE('2024-11-13 02:00:00', '%Y-%m-%d %H:%i:%s')
)
SELECT starttime, endtime FROM time_slots;

PostgreSQL 版本（递归CTE实现） #

语法更简洁，直接用时间加减：

WITH RECURSIVE time_slots AS (
    SELECT 
        '2024-11-13 00:00:00'::TIMESTAMP AS starttime,
        '2024-11-13 00:00:00'::TIMESTAMP + INTERVAL '30 minutes' AS endtime
    UNION ALL
    SELECT 
        endtime AS starttime,
        endtime + INTERVAL '30 minutes' AS endtime
    FROM time_slots
    WHERE endtime < '2024-11-13 02:00:00'::TIMESTAMP
)
SELECT starttime, endtime FROM time_slots;

SQL Server 版本（递归CTE实现） #

用DATEADD函数实现时间偏移：

WITH time_slots AS (
    SELECT 
        CAST('2024-11-13 00:00:00' AS DATETIME) AS starttime,
        DATEADD(MINUTE, 30, CAST('2024-11-13 00:00:00' AS DATETIME)) AS endtime
    UNION ALL
    SELECT 
        endtime AS starttime,
        DATEADD(MINUTE, 30, endtime) AS endtime
    FROM time_slots
    WHERE endtime < CAST('2024-11-13 02:00:00' AS DATETIME)
)
SELECT starttime, endtime FROM time_slots;

低版本MySQL（无递归CTE支持） #

如果你的MySQL版本不支持递归，可以用数字生成表来实现，需要根据时间范围调整数字数量：

SELECT 
    DATE_ADD('2024-11-13 00:00:00', INTERVAL (n*30) MINUTE) AS starttime,
    DATE_ADD('2024-11-13 00:00:00', INTERVAL ((n+1)*30) MINUTE) AS endtime
FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    -- 比如要覆盖24小时就继续加UNION ALL SELECT ...直到47
) AS numbers
WHERE DATE_ADD('2024-11-13 00:00:00', INTERVAL ((n+1)*30) MINUTE) <= '2024-11-13 02:00:00';

内容的提问来源于stack exchange，提问作者user1251973