Oracle上与时区相关的systimestamp和时间戳比较 [英] timezone-dependant systimestamp and timestamp comparison on Oracle

问题描述

我遇到了一个奇怪的情况.有人可以解释为什么时间戳和时间戳之间的比较行为如下(取决于会话时区...).另外，在所有情况下，输出值都是相同的.看起来timestamp从会话继承了时区以进行比较，但出于打印目的，它不是吗?

I've run into a weird situation. Could someone explain why comparison between timestamp and timestamp behaves as below (it depends on session timezone...). In addition outputed values are identical in all cases. It looks like timestamp inherits timezone from the session for comparison purposes, but for printing it does not?

查询:

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位生产

Database is in -6 timezone. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

推荐答案

查看以下内容: https://docs.oracle.com/cd/B12037_01/server.101/b10749/ch4datet.htm#1006334

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

When you compare date and timestamp values, Oracle converts the data to the more precise datatype before doing the comparison. For example, if you compare data of TIMESTAMP WITH TIME ZONE datatype with data of TIMESTAMP datatype, Oracle converts the TIMESTAMP data to TIMESTAMP WITH TIME ZONE, using the session time zone. The order of precedence for converting date and timestamp data is as follows: 1. DATE 2. TIMESTAMP 3. TIMESTAMP WITH LOCAL TIME ZONE 4. TIMESTAMP WITH TIME ZONE For any pair of datatypes, Oracle converts the datatype that has a smaller number in the preceding list to the datatype with the larger number.

这篇关于Oracle上与时区相关的systimestamp和时间戳比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！