如何使用SQL CASE子句处理MDT和MST日期格式的日期列条件

一、MDT/MST日期转换的CASE子句示例 #

针对batch_date列包含MDT（山地夏令时）或MST（山地标准时间）格式的需求——MDT格式日期减1天，MST格式保留原值，以下是适配不同存储格式的CASE子句示例：

场景1：batch_date为带时区标识的字符串（如'2024-05-20 14:30:00 MDT'） #

SELECT
    batch_date,
    column2,
    column3,
    CASE
        -- 匹配MDT格式，去除时区标识后转时间并减1天
        WHEN batch_date LIKE '%MDT%' THEN DATEADD(DAY, -1, CAST(REPLACE(batch_date, 'MDT', '') AS TIMESTAMP))
        -- 匹配MST格式，去除时区标识后转时间
        WHEN batch_date LIKE '%MST%' THEN CAST(REPLACE(batch_date, 'MST', '') AS TIMESTAMP)
        -- 兼容其他未知格式，直接转换
        ELSE CAST(batch_date AS TIMESTAMP)
    END AS adjusted_batch_date
FROM table_name;

场景2：batch_date为带时区的时间类型（如TIMESTAMP WITH TIME ZONE） #

SELECT
    batch_date,
    column2,
    column3,
    CASE
        -- 提取时区判断为MDT时减1天
        WHEN EXTRACT(TIMEZONE FROM batch_date) = 'MDT' THEN DATEADD(DAY, -1, batch_date)
        -- MST时区直接保留原值
        WHEN EXTRACT(TIMEZONE FROM batch_date) = 'MST' THEN batch_date
        -- 其他时区默认保留原值
        ELSE batch_date
    END AS adjusted_batch_date
FROM table_name;

二、Databricks SQL代码验证与建议 #

原代码问题分析 #

你提供的代码逻辑存在明显错误：

batch_track_end_date AS BATCH_TRACK_END_DATE,
CASE 
    WHEN DATE_PART('HOUR', batch_track_end_date) + DATE_PART('MIN', batch_track_end_date) <= 7 
    THEN CAST(DATEADD(DAY, -1, batch_track_end_date) AS DATE) 
    ELSE CAST(batch_track_end_date AS DATE) 
END AS BATCH_TRACK_RPT_DATE

• 小时与分钟直接相加的逻辑不成立：小时和分钟是不同量级的单位（1小时=60分钟），例如00:08的小时+分钟总和为8，会被判定为不满足条件，但实际属于凌晨7点前；而07:00总和为7，会被判定为减1天，这与“早于7点则减1天”的预期不符。

优化后的代码建议 #

推荐使用更直观准确的时间比较方式，Databricks SQL支持直接对时间部分进行判断：

batch_track_end_date AS BATCH_TRACK_END_DATE,
CASE 
    -- 直接判断时间部分是否早于当天7点
    WHEN TIME(batch_track_end_date) < TIME('07:00:00')
    THEN CAST(DATEADD(DAY, -1, batch_track_end_date) AS DATE) 
    ELSE CAST(batch_track_end_date AS DATE) 
END AS BATCH_TRACK_RPT_DATE

如果需要用分钟数计算，需将小时转换为分钟后再相加：

batch_track_end_date AS BATCH_TRACK_END_DATE,
CASE 
    -- 计算当天总分钟数，判断是否小于等于7*60（即7点）
    WHEN DATE_PART('HOUR', batch_track_end_date)*60 + DATE_PART('MINUTE', batch_track_end_date) <= 420
    THEN CAST(DATEADD(DAY, -1, batch_track_end_date) AS DATE) 
    ELSE CAST(batch_track_end_date AS DATE) 
END AS BATCH_TRACK_RPT_DATE

两种写法中，第一种直接比较时间的方式可读性更高，更不易出错。

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