我在两个不同的数据库中运行了相同的语句：我的本地数据库和Oracle Live 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;- （所有语句几乎同时执行-欧洲中部时间上午09：35）*
来自本地数据库的结果：
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的结果：
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看到结果后，我的问题是：
- 为什么Oracle Live的TIMESTAMP显示不同时区的日期（8.35 AM而不是9.35 AM）？
- 为什么Oracle Live的TIMESTAMP WITH TIME ZONE将US/PACIFIC返回为时区？
- 时间戳和带本地时区的时间戳有什么区别吗？
1条答案
按热度按时间06odsfpq1#
文档中介绍了不同的数据类型。
TIMESTAMP数据类型是DATE数据类型的扩展。它存储年、月、日、小时、分钟和秒值。它还存储DATE数据类型不存储的小数秒。
TIMESTAMP WITH TIME ZONE是TIMESTAMP的变体，它的值中包括时区区域名称或时区偏移量。
TIMESTAMP WITH LOCAL TIME ZONE是TIMESTAMP的另一个变体。它与TIMESTAMP WITH TIME ZONE的区别如下：存储在数据库中的数据将规范化为数据库时区，并且时区偏移不会存储为列数据的一部分。当用户检索数据时，Oracle数据库将以用户的本地会话时区返回数据。
您看到的差异是因为时区不同，并且将值默认为
SYSDATE，即系统DATE。在您的本地数据库中，系统时区（
select dbtimezone from dual）似乎基于CET，而Live SQL数据库似乎基于UTC，正如Oracle所建议的那样。由于CET比UTC/GMT早一个小时，这就解释了一个小时的差异。TIMESTAMP值只是一个简单的类型转换，即cast(SYSDATE as TIMESTAMP），因此您得到的值与直接查询SYSDATE时得到的值相同，只是增加了零秒。对于
TIMESTAMP WITH TIME ZONE，它必须存储一个时区，它必须从某个地方获取该时区，默认情况下，它使用您的 * 会话 * 时区，而不是数据库时区。在您的本地DB中，似乎也是CET，但Live SQL将 * 会话 * 时区默认为美国太平洋时间-并非不合理，因此，现在它有效地为该值执行from_tz(cast(SYSDATE as TIMESTAMP), SESSIONTIMEZONE)，其中SESSIONTIMEZONE在一个数据库中是CET，在另一个数据库中是US/Pacific。对于
TIMESTAMP WITH LOCAL TIME ZONE，它也在做同样的事情，但随后将其标准化回数据库时区以进行存储（实际上是cast(from_tz(cast(SYSDATE as TIMESTAMP), SESSIONTIMEZONE) at time zone DBTIMEZONE as TIMESTAMP)-实际上内部不是这样，但给了您一个想法），并在查询时再次从数据库时区转换回您的会话时区。在这两个数据库中，如果在插入前使用
alter session set time_zone = ...，在查询前再次使用不同的值，那么您将看到不同的结果-前两列显示的时间部分将保持不变，但WITH TIME ZONE的时区将发生变化，WITH LOCAL TIME ZONE的时间将发生变化。具有不同会话时区的fiddle。
您可以在我上面链接的文档中阅读更多关于所有这些行为的信息。
如果使用
SYSTIMESTAMP而不是SYSDATE作为所有列的默认值，则可以避免将WITH TIME ZONE值隐式转换为会话时区，这样将始终显示数据库时区。LOCAL列仍将显示在会话时区中。但是它们都表示相同的时间。您仍然会看到两个数据库之间有一个小时的差异，因为它们具有不同的 database 时区。您可以考虑将普通时间戳默认为sys_extract_utc(SYSTIMESTAMP)，或将它们全部（或至少前两个）缺省为X1 M19 N1 X。fiddle和UTC标准化值。