在Trino中转换VARCHAR类型时间戳为yyyyMMdd日期并过滤数据

问题分析 #

原语句直接用字符串比较load_ts > '20220415'无法正确筛选，因为load_ts是yyyy-MM-dd HH:mm:ss.SSS格式的字符串，与yyyyMMdd格式的字符串进行字典序比较会得到错误结果，必须先将load_ts转换为时间类型再处理。

解决方案SQL #

SELECT 
    *,
    -- 将load_ts转换为yyyyMMdd格式的日期字符串
    date_format(cast(load_ts AS TIMESTAMP), '%Y%m%d') AS load_date_yyyyMMdd
FROM 
    your_table
WHERE 
    -- 筛选更新时间在2022-04-15之后的记录
    cast(load_ts AS TIMESTAMP) > DATE '2022-04-15';

关键步骤说明 #

• 类型转换：cast(load_ts AS TIMESTAMP) 将VARCHAR类型的时间戳字符串解析为Trino的TIMESTAMP类型，确保时间值被正确识别。
• 格式转换：date_format(..., '%Y%m%d') 将TIMESTAMP类型格式化为yyyyMMdd格式的字符串，满足日期格式需求。
• 条件筛选：用转换后的TIMESTAMP与DATE '2022-04-15'进行比较，基于时间逻辑而非字符串字典序，保证筛选结果准确。

可选写法 #

如果需要更精确的时间边界，可以将目标日期转换为TIMESTAMP：

WHERE cast(load_ts AS TIMESTAMP) > TIMESTAMP '2022-04-15 00:00:00.000';

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