问题
设CCD_ 1为CCD_。如何从SYS_EXTRACT_UTC(cast(original_date as timestamp ))的结果中获取其值，仅通过Oracle内部函数获取，即与数据库的任何时区设置无关。
背景
一些愚蠢的程序将日期值写入UTC中的一个表，即
cast(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date)
存储在此表的日期类型列中，而不是SYSDATE。
在所有其他表中，SYSDATE只存储在这些列中。我的任务是将这些值一起使用，所以我想恢复SYS_EXTRACT_UTC（）函数的效果。只有手动指定时区，即通过，我才能解决此问题
cast( FROM_TZ(cast(my_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Budapest' as date)
但如果我使用DBTIMEZONE而不是'Europe/Budapest'，那么我会得到错误的结果，可能是因为DBTIMEZONE 忽略了夏季夏令时
例如，当to_char（SYSDATE，'YYYY-MM-DD HH24:MI:SS'）='2016-05-19 13:45:12'时，程序存储
cast(SYS_EXTRACT_UTC(cast(SYSDATE as timestamp)) as date)
我的测试查询是：
SELECT
original_date,
stored_utc_date,
cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Budapest' as date) as reverted_good,
cast( FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong
from (
select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date
from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
)
其结果是：
ORIGINAL_DATE STORED_UTC_DATE REVERTED_GOOD REVERTED_WRONG
------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 11:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12
您可以获得systimestamp时区区域并使用它：
FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE to_char(systimestamp, 'TZR')
根据您的测试数据（但将布达佩斯改为伦敦，因为那是我的本地区）：
SELECT
original_date,
stored_utc_date,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London' as date) as reverted_good,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE TO_CHAR(systimestamp, 'TZR') as date) as reverted_right
from (
select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date
from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
)
/
ORIGINAL_DATE STORED_UTC_DATE REVERTED_GOOD REVERTED_WRONG REVERTED_RIGHT
------------------- ------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12
除了。。。这并不总是有效的，因为TZR被报告为偏移量（因为它基于操作系统TZ），并且你不能从偏移量中猜测区域。如果最初的日期是在冬天，而你在夏天运行，反之亦然，那么恢复的日期将是一个小时。因此，实际上一半的恢复日期总是错误的，但哪一半取决于您何时运行查询。
看起来你可以通过使用DBTIMEZONE作为本地时区来解决这个问题：
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE
as timestamp with local time zone
您的测试查询再次出现：
SELECT
original_date,
stored_utc_date,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London' as date) as reverted_good,
cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as date) as reverted_wrong,
cast(cast(FROM_TZ(cast(stored_utc_date as TIMESTAMP), 'UTC') AT TIME ZONE DBTIMEZONE as timestamp with local time zone) as date) as reverted_right
from (
select original_date, cast( SYS_EXTRACT_UTC(cast(original_date as timestamp )) as date) stored_utc_date
from (select to_date('2016-05-19 13:45:12','YYYY-MM-DD HH24:MI:SS') original_date from dual)
)
/
ORIGINAL_DATE STORED_UTC_DATE REVERTED_GOOD REVERTED_WRONG REVERTED_RIGHT
------------------- ------------------- ------------------- ------------------- -------------------
2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12 2016-05-19 12:45:12 2016-05-19 13:45:12
具有全年日期的更广泛的测试查询：
with t as (
select from_tz(cast(add_months(trunc(sysdate, 'MM'), 1-level) as timestamp), 'Europe/London')
as original_systimestamp
from dual
connect by level <= 12
)
select original_systimestamp,
cast(cast(from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone dbtimezone as timestamp with local time zone) as date) as good_date,
sys_extract_utc(original_systimestamp) as utc_timestamp,
from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone to_char(systimestamp, 'TZR') as at_systimezone,
from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone dbtimezone as at_dbtimezone,
cast(from_tz(sys_extract_utc(original_systimestamp), 'UTC')
at time zone dbtimezone as timestamp with local time zone) as at_local_dbtimezone
from t
order by original_systimestamp;
ORIGINAL_SYSTIMESTAMP GOOD_DATE UTC_TIMESTAMP AT_SYSTIMEZONE AT_DBTIMEZONE AT_LOCAL_DBTIMEZONE
----------------------------------- ------------------- --------------------- ---------------------------- ---------------------------- ----------------------------
2015-06-01 00:00:00.0 Europe/London 2015-06-01 00:00:00 2015-05-31 23:00:00.0 2015-06-01 00:00:00.0 +01:00 2015-05-31 23:00:00.0 +00:00 2015-06-01 00:00:00.0
2015-07-01 00:00:00.0 Europe/London 2015-07-01 00:00:00 2015-06-30 23:00:00.0 2015-07-01 00:00:00.0 +01:00 2015-06-30 23:00:00.0 +00:00 2015-07-01 00:00:00.0
2015-08-01 00:00:00.0 Europe/London 2015-08-01 00:00:00 2015-07-31 23:00:00.0 2015-08-01 00:00:00.0 +01:00 2015-07-31 23:00:00.0 +00:00 2015-08-01 00:00:00.0
2015-09-01 00:00:00.0 Europe/London 2015-09-01 00:00:00 2015-08-31 23:00:00.0 2015-09-01 00:00:00.0 +01:00 2015-08-31 23:00:00.0 +00:00 2015-09-01 00:00:00.0
2015-10-01 00:00:00.0 Europe/London 2015-10-01 00:00:00 2015-09-30 23:00:00.0 2015-10-01 00:00:00.0 +01:00 2015-09-30 23:00:00.0 +00:00 2015-10-01 00:00:00.0
2015-11-01 00:00:00.0 Europe/London 2015-11-01 00:00:00 2015-11-01 00:00:00.0 2015-11-01 01:00:00.0 +01:00 2015-11-01 00:00:00.0 +00:00 2015-11-01 00:00:00.0
2015-12-01 00:00:00.0 Europe/London 2015-12-01 00:00:00 2015-12-01 00:00:00.0 2015-12-01 01:00:00.0 +01:00 2015-12-01 00:00:00.0 +00:00 2015-12-01 00:00:00.0
2016-01-01 00:00:00.0 Europe/London 2016-01-01 00:00:00 2016-01-01 00:00:00.0 2016-01-01 01:00:00.0 +01:00 2016-01-01 00:00:00.0 +00:00 2016-01-01 00:00:00.0
2016-02-01 00:00:00.0 Europe/London 2016-02-01 00:00:00 2016-02-01 00:00:00.0 2016-02-01 01:00:00.0 +01:00 2016-02-01 00:00:00.0 +00:00 2016-02-01 00:00:00.0
2016-03-01 00:00:00.0 Europe/London 2016-03-01 00:00:00 2016-03-01 00:00:00.0 2016-03-01 01:00:00.0 +01:00 2016-03-01 00:00:00.0 +00:00 2016-03-01 00:00:00.0
2016-04-01 00:00:00.0 Europe/London 2016-04-01 00:00:00 2016-03-31 23:00:00.0 2016-04-01 00:00:00.0 +01:00 2016-03-31 23:00:00.0 +00:00 2016-04-01 00:00:00.0
2016-05-01 00:00:00.0 Europe/London 2016-05-01 00:00:00 2016-04-30 23:00:00.0 2016-05-01 00:00:00.0 +01:00 2016-04-30 23:00:00.0 +00:00 2016-05-01 00:00:00.0
但即使这样，也只有当会话时区与数据库服务器的区域匹配时才有效；如果我将会话时区设置为欧洲/伦敦以外的其他地方，那就太远了。如果你依赖于能够设置会话时区，那么问题中第一个带有硬编码区域的查询也不会更糟。。。
同样值得注意的是，DBTIMEZONE不一定会告诉你任何有用的东西；Oracle建议将其设置为UTC。因此，如果你不能使用它，也不能真正使用从systimestamp中提取的TZR（因为它实际上是一个偏移，你不能将其转换为一个区域，所以不能知道DST信息），我认为没有办法从数据库中的任何信息中计算出原始系统时间。似乎您需要在某个时刻提供服务器时区区域——要么通过设置会话时区，要么像在原始查询中一样。