TIMESTAMP三类时间类型差异及Oracle跨库测试疑问

我在本地Oracle数据库和Oracle Live SQL环境中执行了以下SQL语句：

CREATE TABLE test(
    timestamp TIMESTAMP DEFAULT SYSDATE,
    timestamp_tmz TIMESTAMP WITH TIME ZONE DEFAULT SYSDATE,
    timestamp_local_tmz TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE
);

INSERT INTO test VALUES (DEFAULT, DEFAULT, DEFAULT);

SELECT * FROM test;

所有语句均在CET时间09:35 AM左右执行，得到以下结果：

本地数据库结果 #

TIMESTAMP: 10-JAN-23 09.35.32.000000000 AM
TIMESTAMP WITH TIME ZONE: 10-JAN-23 09.35.32.000000000 AM EUROPE/BERLIN
TIMESTAMP WITH LOCAL TIME ZONE: 10-JAN-23 09.35.32.000000000 AM

Oracle Live SQL结果 #

TIMESTAMP: 10-JAN-23 08.35.44.000000 AM 
TIMESTAMP WITH TIME ZONE: 10-JAN-23 08.35.44.000000 AM US/PACIFIC   
TIMESTAMP WITH LOCAL TIME ZONE: 10-JAN-23 08.35.44.000000 AM

针对上述结果的疑问，解答如下：

1. 为何Oracle Live SQL的TIMESTAMP显示8:35 AM而非本地的9:35 AM？ #

TIMESTAMP类型本身不存储时区信息，它存储的是数据库服务器所在时区的本地时间。Oracle Live SQL的服务器部署在US/Pacific时区，当你在CET时间09:35提交请求时，Live SQL服务器的本地时间是08:35 AM，SYSDATE返回的就是这个服务器本地时间，因此TIMESTAMP字段存储并显示这个值。

2. 为何Oracle Live SQL的TIMESTAMP WITH TIME ZONE返回US/PACIFIC时区？ #

TIMESTAMP WITH TIME ZONE会同时存储时间和对应的时区信息。SYSDATE返回的是服务器本地时间，Oracle会自动为这个时间附加服务器的时区标识。由于Live SQL的数据库服务器时区配置为US/PACIFIC，所以该字段查询时会显示这个时区。

3. TIMESTAMP与TIMESTAMP WITH LOCAL TIME ZONE之间是否存在差异？ #

两者差异显著：

• 存储逻辑不同：
  • TIMESTAMP：只存日期和时间，不带任何时区属性，存储的是数据库服务器的本地时间，值固定不变。
  • TIMESTAMP WITH LOCAL TIME ZONE：存储时会把输入时间转换为数据库时区的时间；查询时，会自动将存储的时间转换为当前会话的时区时间返回。
• 时区感知能力不同：
  • TIMESTAMP是无时区感知的，无论哪个时区的会话查询，看到的都是同一个存储值。
  • TIMESTAMP WITH LOCAL TIME ZONE是时区敏感的，不同时区的会话查询会得到对应时区的本地时间。你这次查询结果显示和TIMESTAMP值一致，是因为你的会话时区和Live SQL服务器时区（US/Pacific）相同，转换后没有变化。

内容的提问来源于stack exchange，提问作者Nicolò Vitelli