Oracle Timestamp查询返回早于指定时间的数据问题求助

我编写了如下简化的Oracle查询语句：

SELECT 
    id, value, time,
    to_char(time, 'YYYY-MM-DD"T"HH24:mi:ss') as display_time,
FROM 
    observation
WHERE 
    time >= to_timestamp('2022-10-26T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') 
    AND time < to_timestamp('2022-10-29T23:59:00', 'YYYY-MM-DD"T"HH24:MI:SS') 
ORDER BY 
    time

但查询结果中出现了时间为2022-10-25T21:00:00的数据。time列的类型为timestamp，为何会返回早于指定时间的数据？

完整的查询语句如下：

WITH series_ids AS 
(
    SELECT ds.id 
    FROM data_series ds
    INNER JOIN primary_series ps ON ps.id = ds.id
    INNER JOIN station s ON s.id = ps.station_id
    INNER JOIN parameter p ON p.id = ds.parameter_id
    WHERE ps.station_id IN (3230,3231,3232)
      AND p.id IN (474)      
), 
observations AS 
(
    SELECT 
        id, series_id, value, time,
        to_char(time, 'YYYY-MM-DD"T"HH24:MI:SS') as display_time
    FROM 
        observation
    WHERE 
        time >= to_timestamp('2022-10-26T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') AND
        time < to_timestamp('2022-10-29T23:59:00', 'YYYY-MM-DD"T"HH24:MI:SS') AND
        series_id in (SELECT id FROM series_ids)
    ORDER BY 
        time
    FETCH FIRST 100 ROWS ONLY
) 
SELECT o.* 
FROM observations o 
INNER JOIN series_ids ds ON ds.id = o.series_id

查询结果里包含了时间为2022-10-25T21:00:00的记录，不符合WHERE子句的时间过滤条件。

问题原因及解决办法 #

1. 核心原因：时区不匹配 #

这大概率是时区差异导致的：

• 你以为time列是普通TIMESTAMP，但实际可能是TIMESTAMP WITH TIME ZONE或TIMESTAMP WITH LOCAL TIME ZONE类型（Oracle有时会隐性处理时区转换）。
• 你用to_timestamp()生成的是不带时区的时间戳，Oracle会自动将其转换为当前会话时区的时间，再和存储的时间比较。如果存储的时间和会话时区不一致，就会出现过滤范围偏差。
• 比如：如果数据库存储的是UTC时间，而你的会话时区是UTC-3，那么to_timestamp('2022-10-26T00:00:00')会被当成UTC-3的本地时间，转换为UTC就是2022-10-26T03:00:00。此时若你预期过滤的是UTC时间2022-10-26T00:00:00及以后的记录，实际过滤条件就会偏晚，导致更早的记录被误选。

2. 先确认列的真实类型 #

执行以下语句检查time列的实际类型：

SELECT data_type, time_zone_name
FROM all_tab_columns 
WHERE table_name = 'OBSERVATION' 
  AND column_name = 'TIME';

如果返回TIMESTAMP WITH TIME ZONE或TIMESTAMP WITH LOCAL TIME ZONE，必须用带时区的时间戳进行比较。

3. 修正查询的时间过滤条件 #

把to_timestamp替换为to_timestamp_tz，并明确指定时区，确保和存储的时间时区一致：
比如，如果存储的是UTC时间，修改WHERE子句如下：

WHERE 
    time >= to_timestamp_tz('2022-10-26T00:00:00 UTC', 'YYYY-MM-DD"T"HH24:MI:SS TZR') AND
    time < to_timestamp_tz('2022-10-29T23:59:00 UTC', 'YYYY-MM-DD"T"HH24:MI:SS TZR')

如果存储的是其他时区，把UTC替换成对应的时区标识即可（比如America/New_York）。

4. 排除其他因素 #

你的查询中，observations子查询已经先过滤了series_id，最后和series_ids关联不会引入新数据；FETCH FIRST 100 ROWS ONLY是在过滤后执行的，也不会导致早于条件的记录被选中，所以可以排除这两个因素。

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