Presto SQL技术问询：查询当前至36小时前的所有记录

需求 #

无需手动更新日期字符串，实现查询当前时间到36小时前的所有记录。

原WHERE条件示例 #

-- DATE_FORMAT(FROM_UNIXTIME(desk.reported_date, 'UTC'), '%Y-%m-%d %T') BETWEEN '2024-01-02 06:00:00.000' AND '2024-12-31 23:59:59.000'

我的尝试 #

-- DATE_FORMAT(FROM_UNIXTIME(desk.submit_date, 'UTC'),'%Y-%m-%d %T') > (CURRENT_DATE - INTERVAL '36' HOUR)
-- CAST (DATE_FORMAT(FROM_UNIXTIME(desk.submit_date, 'UTC'),'%Y-%m-%d %T') AS DATE) > CURRENT_DATE - INTERVAL '1' HOUR 
DATE_FORMAT(FROM_UNIXTIME(desk.submit_date, 'UTC'), '%Y-%m-%d %T') BETWEEN  '2024-01-02 06:00:00.000' AND date_add('hour', desk.submit_date, date_parse(-36, '%Y-%m-%d'))

常见错误 #

无法从日期中减去小时、分钟或秒

正确解决方案 #

方案1：直接使用UTC时间函数比较（推荐） #

desk.submit_date是Unix时间戳，直接转换为UTC datetime后与时间范围比较，避免字符串转换，效率更高：

WHERE FROM_UNIXTIME(desk.submit_date, 'UTC') BETWEEN UTC_TIMESTAMP() - INTERVAL 36 HOUR AND UTC_TIMESTAMP()

方案2：保留字符串格式比较（不推荐，效率低） #

若必须使用DATE_FORMAT转换后的字符串，需确保右侧为带时间的字符串格式：

WHERE DATE_FORMAT(FROM_UNIXTIME(desk.submit_date, 'UTC'), '%Y-%m-%d %T') 
BETWEEN DATE_FORMAT(UTC_TIMESTAMP() - INTERVAL 36 HOUR, '%Y-%m-%d %T') 
AND DATE_FORMAT(UTC_TIMESTAMP(), '%Y-%m-%d %T')

错误原因说明 #

1. CURRENT_DATE是仅含年月日的日期类型，无法直接加减小时，需使用带时分秒的时间函数如UTC_TIMESTAMP()或NOW()
2. date_add语法错误，正确用法为DATE_ADD(目标日期, INTERVAL 数值 单位)，例如DATE_ADD(UTC_TIMESTAMP(), INTERVAL -36 HOUR)
3. 避免将时间转换为字符串后比较，直接用datetime类型比较性能更优，且不易出现格式匹配问题

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