sql - 将 DATE 转换为 UTC 时间的 DATE : sys_extract_utc behaving differently for sysdate and systimestamp

Question

select sysdate from dual;    
15-NOV-2018 23:09:31

select cast(sysdate as TIMESTAMP) from dual;
15-NOV-18 11.07.47.000000000 PM

select sys_extract_utc(cast(sysdate as TIMESTAMP)) from dual;
15-NOV-18 05.40.35.000000000 PM

但，

select systimestamp from dual;
15-NOV-18 11.11.16.345959000 PM -08:00

select sys_extract_utc(systimestamp) from dual;
16-NOV-18 07.08.49.772214000 AM

更远，

select cast(sysdate as TIMESTAMP) at time zone 'UTC' from dual;
15-NOV-18 05.48.11.000000000 PM UTC

为什么是 sys_extract_utc(cast(sysdate as TIMESTAMP))不同于 sys_extract_utc(systimestamp)在我的数据库中？ sys_extract_utc(systimestamp)给出的时间是正确的UTC时间，顺便说一句。
我的核心要求是在选择查询中将具有 DATE 列的表中的数据转换为“UTC 时区中的日期”。它当然不起作用(使用 sys_extract_utc 或使用 at time zone 'UTC' )，这可能是因为与 sysdate 的上述转换不起作用的相同原因。

如果有帮助，dbtimezone 是:

SELECT DBTIMEZONE FROM DUAL;
+00:00

Answer 1

功能 SYS_EXTRACT_UTC仅适用于 TIMESTAMP WITH TIME ZONE或 TIMESTAMP WITH LOCAL TIME ZONE值。 DATE和 TIMESTAMP不包含任何时区信息，所以 SYS_EXTRACT_UTC在DATE上基本没用或 TIMESTAMP值。

当你跑

SYS_EXTRACT_UTC(CAST(SYSDATE AS TIMESTAMP))

然后 Oracle 执行隐式转换并实际运行

SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE))

SYSDATE和 SYSTIMESTAMP返回数据库服务器操作系统时区的时间( NOT DBTIMEZONE )而您的命令使用当前 session SESSIONTIMEZONE
如果你坚持使用 DATE值那么你可以使用这个:

SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr')))

TO_CHAR(SYSTIMESTAMP, 'tzr')为您提供数据库服务器操作系统的时区。

请注意，通常数据库服务器操作系统的时区不是像 Europe/Zurich 这样的区域。但在 +01:00 之间每年更改两次和 +02:00由于夏令时。对于 SYSDATE和 SYSTIMESTAMP这没关系，因为 当前 UTC 偏移量始终正确。但例如，如果我的表包含一个 DATE 值 2018-08-01 12:00:00那么如果您在 11 月的今天从上面运行查询，您将得到错误的结果。

如果您的数据库服务器使用夏令时，那么最好使用类似的查询

SYS_EXTRACT_UTC(FROM_TZ(CAST({arbitrary DATE value} AS TIMESTAMP), 'Europe/Zurich'))