Oracle上与时区相关的systimestamp和时间戳比较

Question

我遇到了一个奇怪的情况。谁能解释一下为什么时间戳和时间戳之间的比较行为如下(它取决于会话时区...)。此外，输出值在所有情况下都是相同的。出于比较的目的，时间戳似乎继承了会话的时区，但对于打印，它不是这样的吗？

查询：

alter session set time_zone = '-6:0';
select cast(systimestamp as timestamp), systimestamp, case when cast(systimestamp as timestamp) < systimestamp then 'timestamp < systm' else 'timestamp >= systm' end as cmp from dual;
alter session set time_zone = '1:0';
select cast(systimestamp as timestamp), systimestamp, case when cast(systimestamp as timestamp) < systimestamp then 'timestamp < systm' else 'timestamp >= systm' end as cmp from dual;

输出：

CAST(SYSTIMESTAMPASTIMESTAMP) SYSTIMESTAMP                        CMP              
----------------------------- ----------------------------------- ------------------
14/02/06 21:22:05,319973000   14/02/06 21:22:05,319973000 -06:00  timestamp >= systm 

session SET altered.
CAST(SYSTIMESTAMPASTIMESTAMP) SYSTIMESTAMP                        CMP              
----------------------------- ----------------------------------- ------------------
14/02/06 21:22:06,057183000   14/02/06 21:22:06,057183000 -06:00  timestamp < systm  

数据库位于-6时区。Oracle Database 11g企业版11.2.0.3.0 -64位生产版

Answer 1

看看这个：https://docs.oracle.com/cd/B12037_01/server.101/b10749/ch4datet.htm#1006334

当您比较日期和时间戳值时，

会在进行比较之前将数据转换为更精确的数据类型。例如，如果将TIMESTAMP WITH TIME ZONE数据类型的数据与TIMESTAMP数据类型的数据进行比较，Oracle会使用会话时区将TIMESTAMP数据转换为TIMESTAMP with TIME zone。转换日期和时间戳数据的优先顺序如下: 1.日期2.时间戳3.具有本地时区的时间戳4.具有时区的时间戳对于任何一对数据类型，Oracle会将上述列表中数字较小的数据类型转换为数字较大的数据类型。

Answer 2

SYSTIMESTAMP返回TIMESTAMP WITH TIME ZONE数据类型。看起来Oracle将TIMESTAMP转换为TIMESTAMP WITH TIME ZONE数据类型以进行比较，实际上Oracle执行此操作：

SELECT
    CASE 
    WHEN CAST(CAST(SYSTIMESTAMP AS TIMESTAMP) AS TIMESTAMP WITH TIME ZONE) < SYSTIMESTAMP 
        THEN 'timestamp < systm' 
        ELSE 'timestamp >= systm' 
    END AS cmp 
FROM dual;

对于它采用会话时区的转换，您可以使用以下查询进行检查：

SELECT 
    EXTRACT(TIMEZONE_HOUR FROM CAST(CAST(SYSTIMESTAMP AS TIMESTAMP) AS TIMESTAMP WITH TIME ZONE)) AS TZ_HOUR
FROM dual;

您可以讨论这是否有意义。为了正确的转换，你最好使用FROM_TZ函数，然后它在你的控制之下。SYSTIMESTAMP返回DB时区的时间戳，所以正确的语句应该是这样的：

SELECT FROM_TZ(LOCALTIMESTAMP, DBTIMEZONE), SYSTIMESTAMP, 
    CASE FROM_TZ(LOCALTIMESTAMP, DBTIMEZONE) < SYSTIMESTAMP THEN
    ...