Presto SQL日期范围查询参数化失效及PostgreSQL适配咨询

我有一个包含多个子查询的大型SQL语句，通过WITH语句绑定，且所有子查询使用相同的日期筛选条件。我希望仅在一处更新日期上下界，即可同步所有WHERE条件。当前我定义了如下param CTE来存储起始和结束时间戳：

param AS (                        
    SELECT TIMESTAMP '2024-03-23 23:59:50' AS start_date, TIMESTAMP '2024-03-23 23:59:59' AS end_date  
),

WHERE条件写法为：

(FROM_UNIXTIME(hpd_help_desk.submit_date, 'UTC') BETWEEN (SELECT start_date from param) and (SELECT end_date from param))

但该语句未返回指定日期范围内的数据，请问在PostgreSQL中是否需要使用不同的函数或方法？

问题分析与解决方案 #

• 替换MySQL专属函数：PostgreSQL不存在FROM_UNIXTIME函数，这是导致无数据返回的核心原因。要将Unix时间戳转换为时间类型，需使用PostgreSQL自带的TO_TIMESTAMP函数：

  • 若submit_date是秒级时间戳：TO_TIMESTAMP(hpd_help_desk.submit_date)
  • 若submit_date是毫秒级时间戳：TO_TIMESTAMP(hpd_help_desk.submit_date / 1000)

• 优化CTE引用方式：无需重复写子查询(SELECT start_date from param)，通过CROSS JOIN将param CTE与主表关联，直接引用字段，写法更简洁且性能更优：

WITH param AS (                        
    SELECT 
        TIMESTAMP '2024-03-23 23:59:50' AS start_date, 
        TIMESTAMP '2024-03-23 23:59:59' AS end_date  
)
SELECT *
FROM hpd_help_desk
CROSS JOIN param
WHERE TO_TIMESTAMP(hpd_help_desk.submit_date) BETWEEN param.start_date AND param.end_date;

• 时区精准控制：如果需要严格基于UTC时区筛选，需将CTE中的时间定义为带时区的TIMESTAMPTZ类型，并确保转换后的时间也指定UTC时区：

WITH param AS (                        
    SELECT 
        TIMESTAMPTZ '2024-03-23 23:59:50+00' AS start_date, 
        TIMESTAMPTZ '2024-03-23 23:59:59+00' AS end_date  
)
SELECT *
FROM hpd_help_desk
CROSS JOIN param
WHERE TO_TIMESTAMP(hpd_help_desk.submit_date) AT TIME ZONE 'UTC' BETWEEN param.start_date AND param.end_date;

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