WHERE子句中使用FROM_TZ未返回正确结果，为何SQL未返回2行？

WITH dates AS (
    SELECT TO_DATE('17/01/2023 11:42', 'dd/mm/yyyy hh24:mi') dst_time FROM dual 
    UNION ALL 
    SELECT TO_DATE('17/01/2023 17:35', 'dd/mm/yyyy hh24:mi') dst_time FROM dual
)
SELECT FROM_TZ(CAST(TO_DATE('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' begin_time,  
       FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' end_time,
      dst_time
FROM dates
WHERE dst_time BETWEEN FROM_TZ(CAST(to_date('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' 
                    AND FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT'  
ORDER BY dst_time DESC;

注意：FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT'返回的是22:00，但查询结果未返回17/01/2023 17:35这一行，十分奇怪。

问题根源 #

问题出在数据类型不匹配引发的时区转换逻辑偏差：

• dst_time是DATE类型，Oracle的DATE无时区属性，本质存储的是当前会话时区的时间戳。
• WHERE子句中BETWEEN两端是TIMESTAMP WITH TIME ZONE类型（已转换为GMT时区）。当DATE与带时区的TIMESTAMP比较时，Oracle会自动将DATE转换为当前会话时区的TIMESTAMP WITH TIME ZONE，再与GMT时间做对比。

举个实际场景：如果你的会话时区是US/EASTERN，那么TO_DATE('17/01/2023 17:35', ...)会被转换为17/01/2023 17:35 US/EASTERN，转成GMT后是17/01/2023 22:35 GMT，这明显大于BETWEEN的上限22:00 GMT，因此该行被过滤。

解决方案 #

要保证比较逻辑准确，需让两边的时间类型及时区统一，两种常用处理方式：

方法1：将dst_time转换为GMT时区的带时区时间

假设dst_time实际存储的是US/EASTERN时区的时间，修改WHERE子句：

WHERE FROM_TZ(CAST(dst_time AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT'
      BETWEEN FROM_TZ(CAST(to_date('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT' 
          AND FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'GMT'

方法2：将BETWEEN两端的GMT时间转换为US/EASTERN时区的DATE

既然dst_time是US/EASTERN时区的DATE，直接把过滤条件转成对应时区的时间：

WHERE dst_time
      BETWEEN CAST(FROM_TZ(CAST(to_date('17/01/2023 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'US/EASTERN' AS DATE)
          AND CAST(FROM_TZ(CAST(to_date('17/01/2023 17:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP), 'US/EASTERN') AT TIME ZONE 'US/EASTERN' AS DATE)

（注：如果过滤条件原本就是基于US/EASTERN时区的时间，可直接简化为TO_DATE('17/01/2023 00:00:00', ...)和TO_DATE('17/01/2023 17:00:00', ...)）

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