技术问询：生成小时时间槽及解析weekdayMask的SQL实现

Alright, let's work through these two SQL tasks step by step. I'll provide practical examples that should be adaptable to most popular SQL databases (with small tweaks for dialect differences).

The easiest way to create hourly time slots is using a recursive Common Table Expression (CTE) — this works in PostgreSQL, SQL Server, and MySQL 8.0+. Here's how to do it:

WITH hourly_slots AS (
    -- Start with our initial time slot
    SELECT 
        CAST('2024-05-01 09:00:00' AS DATETIME) AS slot_start,
        CAST('2024-05-01 10:00:00' AS DATETIME) AS slot_end
    UNION ALL
    -- Recursively add 1 hour to each slot until we hit the end date
    SELECT 
        DATEADD(HOUR, 1, slot_start),
        DATEADD(HOUR, 1, slot_end)
    FROM hourly_slots
    WHERE slot_start < CAST('2024-05-03 17:00:00' AS DATETIME)
)
SELECT slot_start, slot_end
FROM hourly_slots
ORDER BY slot_start;

Quick note: For PostgreSQL, replace DATEADD with interval arithmetic, like slot_start + INTERVAL '1 hour'. Adjust the start/end datetime values to match your specific range.

First, let's assume your weekdayMask uses bit positions mapped to weekdays like this (adjust if your system uses a different order):

• 0x01 = Monday (bit 0)
• 0x02 = Tuesday (bit 1)
• 0x04 = Wednesday (bit 2)
• 0x08 = Thursday (bit 3)
• 0x10 = Friday (bit 4)
• 0x20 = Saturday (bit 5)
• 0x40 = Sunday (bit 6)

We'll use the bitwise & operator to check if each bit is set, then output a 1 (enabled) or 0 (disabled) for each weekday:

SELECT
    scheduleId,
    startDate,
    endDate,
    startTime,
    endTime,
    weekdayMask,
    -- Check each bit to generate weekday flags
    CASE WHEN (weekdayMask & 0x01) != 0 THEN 1 ELSE 0 END AS MONDAY,
    CASE WHEN (weekdayMask & 0x02) != 0 THEN 1 ELSE 0 END AS TUESDAY,
    CASE WHEN (weekdayMask & 0x04) != 0 THEN 1 ELSE 0 END AS WEDNESDAY,
    CASE WHEN (weekdayMask & 0x08) != 0 THEN 1 ELSE 0 END AS THURSDAY,
    CASE WHEN (weekdayMask & 0x10) != 0 THEN 1 ELSE 0 END AS FRIDAY,
    CASE WHEN (weekdayMask & 0x20) != 0 THEN 1 ELSE 0 END AS SATURDAY,
    CASE WHEN (weekdayMask & 0x40) != 0 THEN 1 ELSE 0 END AS SUNDAY
FROM your_schedule_table;

Important: If your system maps Sunday to the first bit (0x01 = Sunday), just reorder the CASE statements to match your actual bit mapping.

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