Oracle查询报错ORA-30084：如何按EST时区筛选前一天数据？

我需要查询PO_HEADERS_ALL表中CREATION_DATE为前一天的数据，该字段在数据库中以UTC时区存储，需转换为EST时区保证筛选准确性。我编写的SQL语句如下：

SELECT  CAST(CREATION_DATE AT TIME ZONE '-04:00' AS DATE )
FROM     PO_HEADERS_ALL    
WHERE 
  CAST(CREATION_DATE AT TIME ZONE '-04:00' AS DATE )   >= CAST(SYSDATE-1 AT TIME ZONE '-04:00' AS DATE )
  AND CAST(CREATION_DATE AT TIME ZONE '-04:00' AS DATE ) < CAST(SYSDATE AT TIME ZONE '-04:00' AS DATE )

执行后触发错误：ORA-30084: invalid data type for datetime primary with time zone modifier

请问如何修改语句，将CREATION_DATE和SYSDATE转换为EST时区，从而正确筛选出前一天的数据？

错误原因 #

SYSDATE是Oracle的DATE类型，不包含时区信息，直接使用AT TIME ZONE操作符会触发类型不匹配错误——该操作符要求操作对象为带时区的 datetime 类型（如TIMESTAMP WITH TIME ZONE）。另外EST存在夏令时切换，直接用固定偏移-04:00无法自动适配冬夏令时的时差变化，建议使用数据库内置的时区名称来处理。

推荐写法（自动适配夏令时） #

使用America/New_York时区名称（Oracle官方对应EST/EDT时区），同时用SYSTIMESTAMP（自带时区信息）替代SYSDATE，避免类型转换问题：

SELECT 
    CAST(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' AS DATE) AS est_creation_date
FROM PO_HEADERS_ALL
WHERE 
    CAST(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' AS DATE)
    >= TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE)) - 1
    AND CAST(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' AS DATE)
    < TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE))

如果要避免重复转换字段提升效率，可以用CTE简化：

WITH converted_headers AS (
    SELECT 
        CAST(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' AS DATE) AS est_creation_date
    FROM PO_HEADERS_ALL
)
SELECT est_creation_date
FROM converted_headers
WHERE 
    est_creation_date >= TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE)) - 1
    AND est_creation_date < TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE))

固定偏移写法（不推荐，夏令时会出错） #

如果确定不需要处理夏令时，仅使用固定偏移-04:00，需要先将SYSDATE转换为带时区的类型：

SELECT 
    CAST(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE '-04:00' AS DATE) AS est_creation_date
FROM PO_HEADERS_ALL
WHERE 
    CAST(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE '-04:00' AS DATE)
    >= TRUNC(CAST(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'UTC') AT TIME ZONE '-04:00' AS DATE)) - 1
    AND CAST(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE '-04:00' AS DATE)
    < TRUNC(CAST(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'UTC') AT TIME ZONE '-04:00' AS DATE))

关键说明 #

• FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC')：将原本无时区的CREATION_DATE（DATE类型）转换为TIMESTAMP WITH TIME ZONE，并指定原始时区为UTC
• AT TIME ZONE 'America/New_York'：自动将UTC时间转换为EST/EDT时区，无需手动切换冬夏令时偏移
• TRUNC()：截断时间部分，确保筛选范围是完整的前一天（从00:00到次日00:00）

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