Oracle:本地时区，区分两小时2

Question

我有一个Oracle表(MYTAB(，其中包含日期时间(MYDT(和值(MYVAL(的列。

MYDT         NOT NULL TIMESTAMP(0) WITH LOCAL TIME ZONE
MYVAL        NOT NULL NUMBER

让我们看看CEST时区到CET时区的切换。10月的最后一个星期天，当地时间都有两个小时的2——第一个小时是CEST，第二个小时是CET。

我的桌子上每小时都有一个条目。假设第一个小时2的MYVAL为100，第二个小时2为200。

我可以正确地处理SELECT中的值。

获得第一个小时2:

SQL> SELECT TO_TIMESTAMP_TZ(MYDT, 'DD.MM.YYYY HH24:MI TZH'), MYDT, MYVAL    
FROM MYTAB 
WHERE MYDT = TO_TIMESTAMP_TZ ('28.10.2018 00:00 00', 'DD.MM.YYYY HH24:MI TZH');
28.10.18 02:00:00 +00:00
28.10.18 02:00:00
111

获得第2小时2:

SQL> SELECT  TO_TIMESTAMP_TZ(MYDT, 'DD.MM.YYYY HH24:MI TZH'), MYDT, MYVAL 
FROM MYTAB 
WHERE MYDT = TO_TIMESTAMP_TZ ('28.10.2018 01:00 00', 'DD.MM.YYYY HH24:MI TZH');
28.10.18 02:00:00 +00:00
28.10.18 02:00:00
222

所以我得到了关于时间戳的正确值。

但在这两种情况下，timessamp都是相等的——这是因为它存储在本地时间中。

如何获取唯一的时间戳？这应该是可能的，因为信息必须在那里，否则SELECT不会在WHERE中为我提供关于时区的正确值。

谢谢！

Answer 1

时间戳值不存储在"localtime"中，而是存储在DBTIMEZONE中。然而，这更多的是Oracle内部的主题，并不是真正相关的。

代替cast(MYDT at time zone 'UTC' as date)，您也可以使用SYS_EXTRACT_UTC(MYDT)

为了让它更清楚，尝试

SELECT 
SYS_EXTRACT_UTC(MYDT), 
TO_CHAR(MYDT, 'dd.mm.yyyy hh24:mi:ss tzd'),
MYDT, MYVAL 
FROM MYTAB;

那么你应该看到全貌。

Answer 2

首先，"我可以正确地处理SELECT中的值"并不完全正确。这些：

TO_TIMESTAMP_TZ(MYDT, 'DD.MM.YYYY HH24:MI TZH')
TO_TIMESTAMP_TZ ('28.10.2018 00:00 00', 'DD.MM.YYYY HH24:MI TZH');

没有做你想做的事。在第一个例子中，您使用会话的NLS_TIMESTAMP_FORMAT设置将mydt值隐式转换为字符串，这会产生类似于"28.10.18 02:00:00"的结果；然后在这两种情况下，您都使用您提供的格式掩码转换字符串。但是时区小时偏移量是由字符串中的秒值提供的。

在您的两个示例中，这意味着TZH设置为零，您可以在输出中看到。如果你的原始值是"28.10.18 02:00:03"，那么它将变成"2018-10-28 02:0:00+03:00"，这根本不是你想要的。如果有任何值的秒数超过14，那么它将失败，并显示"ORA-01874：时区小时必须在-12和14之间"。

因此，您的查询找到了您想要的行，但这几乎是偶然的。

与一些样本数据和会话设置匹配，我认为你有：

create table mytab(
mydt timestamp(0) with local time zone not null,
myval number not null
);
insert into mytab (mydt, myval) values (timestamp '2018-10-28 01:59:59 CET CEST', 1);
insert into mytab (mydt, myval) values (timestamp '2018-10-28 02:00:00 CET CEST', 2);
insert into mytab (mydt, myval) values (timestamp '2018-10-28 02:00:00 CET CET', 3);
insert into mytab (mydt, myval) values (timestamp '2018-10-28 02:00:01 CET CET', 4);
insert into mytab (mydt, myval) values (timestamp '2018-10-28 02:00:02 CET CET', 5);
insert into mytab (mydt, myval) values (timestamp '2018-10-28 02:00:03 CET CET', 6);
alter session set time_zone = 'Europe/Berlin';
alter session set nls_timestamp_format = 'DD.MM.YYYY HH24:MI:SS';
alter session set nls_timestamp_tz_format = 'DD.MM.YYYY HH24:MI:SS TZH:TZM';

然后你可以更清楚地看到问题：

select mydt, to_timestamp_tz(mydt, 'DD.MM.YYYY HH24:MI TZH')
from mytab;
Error report -
ORA-01874: time zone hour must be between -12 and 14
select mydt, to_timestamp_tz(mydt, 'DD.MM.YYYY HH24:MI TZH')
from mytab
where myval > 1;
MYDT                TO_TIMESTAMP_TZ(MYDT,'DD.M
------------------- --------------------------
28.10.2018 02:00:00 28.10.2018 02:00:00 +00:00
28.10.2018 02:00:00 28.10.2018 02:00:00 +00:00
28.10.2018 02:00:01 28.10.2018 02:00:00 +01:00
28.10.2018 02:00:02 28.10.2018 02:00:00 +02:00
28.10.2018 02:00:03 28.10.2018 02:00:00 +03:00

您可能一直在期待类似to_char(mydt, 'DD.MM.YYYY HH24:MI TZH')的内容，但这会导致"ORA-01821：无法识别日期格式"。但是，您可以看到区域和夏令时标志：

select mydt, to_char(mydt, 'DD.MM.YYYY HH24:MI TZR TZD')
from mytab;
MYDT                TO_CHAR(MYDT,'DD.MM.YYYYHH24:MITZRTZD')                 
------------------- --------------------------------------------------------
28.10.2018 01:59:59 28.10.2018 01:59 EUROPE/BERLIN CEST                     
28.10.2018 02:00:00 28.10.2018 02:00 EUROPE/BERLIN CEST                     
28.10.2018 02:00:00 28.10.2018 02:00 EUROPE/BERLIN CET                      
28.10.2018 02:00:01 28.10.2018 02:00 EUROPE/BERLIN CET                      
28.10.2018 02:00:02 28.10.2018 02:00 EUROPE/BERLIN CET                      
28.10.2018 02:00:03 28.10.2018 02:00 EUROPE/BERLIN CET                      

但是，它仍然会使用会话时区显示字符串。

类似地，你的过滤器可能类似于：

select myval,
mydt,
sys_extract_utc(mydt) as mydt_utc,
to_char(mydt, 'DD.MM.YYYY HH24:MI TZR TZD') as mydt_full
from mytab
where mydt = to_timestamp_tz('28.10.2018 02:00:00 CET CEST', 'DD.MM.YYYY HH24:MI:SS TZR TZD');
MYVAL MYDT                MYDT_UTC            MYDT_FULL                          
---------- ------------------- ------------------- -----------------------------------
2 28.10.2018 02:00:00 28.10.2018 00:00:00 28.10.2018 02:00 EUROPE/BERLIN CEST

和

select myval,
mydt,
sys_extract_utc(mydt) as mydt_utc,
to_char(mydt, 'DD.MM.YYYY HH24:MI TZR TZD') as mydt_full
from mytab
where mydt = to_timestamp_tz('28.10.2018 02:00:00 CET CET', 'DD.MM.YYYY HH24:MI:SS TZR TZD');
MYVAL MYDT                MYDT_UTC            MYDT_FULL                          
---------- ------------------- ------------------- -----------------------------------
3 28.10.2018 02:00:00 28.10.2018 01:00:00 28.10.2018 02:00 EUROPE/BERLIN CET 

我已经包含了sys_extract_utc()输出作为您的cast()的替代方案。您也可以使用不带强制转换的mydt at time zone 'UTC'，这将为您提供一个带有时区值的时间戳。

同样，更改会话时区将更改mydt_full值的显示方式，但过滤器仍将工作，因为那里明确提供了正确的时区。根据过滤器值的来源，您可以使用时间戳文字，就像我在insert语句中所做的那样。

阅读有关本地时区数据类型的时间戳的更多信息。

Answer 3

我发现

SELECT cast(MYDT at time zone 'UTC' as date) FROM ...

符合我的要求。现在一切都在UTC，我可以区分。