如何编写计算播送日期的SQL？基于spot_datetime的时间判断

我来帮你搞定这个播送日期的计算需求！先梳理下你原语句里的几个问题，再给你修正后的正确写法：

原语句的问题分析 #

1. 时间格式化逻辑错误：你用了%h（12小时制）提取时间，这会导致下午6点被识别成06:00:00，直接打乱判断逻辑，应该用%H（24小时制），或者更简洁的TIME()函数直接提取时间部分。
2. 时间常量写法错误：cast(06:00:00 as time)缺少引号，正确的写法是CAST('06:00:00' AS TIME)，或者直接用字符串'06:00:00'和时间类型做比较。
3. 核心逻辑反转：你的else分支写了date_add(spot_datetime, interval 1 day)，完全搞反了规则——应该是早于6点的日期减1天，而不是晚于6点加1天。

修正后的SQL（假设spot_datetime已是ET时区） #

如果你的spot_datetime字段存储的已经是ET时区的时间，直接用下面的语句即可：

SELECT 
    spot_datetime,
    CASE
        -- 当时间早于6:00:00，播送日期为前一天
        WHEN TIME(spot_datetime) < '06:00:00' THEN DATE(spot_datetime) - INTERVAL 1 DAY
        -- 否则播送日期就是当天
        ELSE DATE(spot_datetime)
    END AS broadcast_date
FROM TABLE_A;

或者用更简洁的单行写法：

SELECT 
    spot_datetime,
    DATE(spot_datetime) + INTERVAL CASE WHEN TIME(spot_datetime) >= '06:00:00' THEN 0 ELSE -1 END DAY AS broadcast_date
FROM TABLE_A;

需转换时区的情况（比如数据库存的是UTC时间） #

如果你的spot_datetime是UTC时区，需要先转换成ET时区（注意ET包含夏令时，建议用数据库支持的时区名，比如'US/Eastern'）：

SELECT 
    spot_datetime,
    -- 先转换时区到ET，再判断计算播送日期
    CASE
        WHEN TIME(CONVERT_TZ(spot_datetime, 'UTC', 'US/Eastern')) < '06:00:00' THEN
            DATE(CONVERT_TZ(spot_datetime, 'UTC', 'US/Eastern')) - INTERVAL 1 DAY
        ELSE
            DATE(CONVERT_TZ(spot_datetime, 'UTC', 'US/Eastern'))
    END AS broadcast_date
FROM TABLE_A;

验证示例 #

拿你给出的测试数据验证：

• 2019-04-10 17:36:00：时间17:36≥6:00，播送日期为2019-04-10
• 2019-04-05 05:22:00：时间05:22<6:00，播送日期为2019-04-04

完全符合你的需求哦！

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