sql - Oracle:本地时区,区分双小时 2
问题描述
我有一个 Oracle 表(MYTAB),其中包含一个日期时间列(MYDT)和一个值(MYVAL)。
MYDT NOT NULL TIMESTAMP(0) WITH LOCAL TIME ZONE
MYVAL NOT NULL NUMBER
让我们看看时区 CEST 到 CET 的切换。10 月的每个最后一个星期日,当地时间都有一个双小时 2 - 第一个小时 2 在 CEST 中,第二小时在 CET 中。
我的桌子上每小时都有一个条目。假设第一小时 2 的 MYVAL 是 100,第二小时 2 是 200。
我可以正确处理 SELECT 中的值。
获取第 1 小时 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
所以我得到了关于我的时间戳的正确值。
但在这两种情况下,时间戳都是相等的 - 认为那是因为它存储在本地时间。
如何获得唯一的时间戳?这应该是可能的,因为信息必须在那里,否则 SELECT 不会给我关于 WHERE 中时区的正确值。
谢谢!
解决方案
首先,“我可以正确处理 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');
没有按照你的想法做。在第一个中,您正在mydt
使用会话NLS_TIMESTAMP_FORMAT
设置将值隐式转换为字符串,这会给您类似 '28.10.18 02:00:00'; 然后在他们两个中,您都使用您提供的格式掩码转换字符串。但是时区小时偏移量是由字符串中的秒值提供的。
在您的两个示例中,这意味着 TZH 设置为零,您可以在输出中看到。如果您的原始值为“28.10.18 02:00:03”,那么它将变为“2018-10-28 02:00: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
值的显示方式,但过滤器仍然有效,因为在那里明确提供了正确的时区。根据过滤器值的来源,您可以使用时间戳文字,就像我在插入语句中所做的那样。
推荐阅读
- javascript - VueJS - 如何注册自定义元素
, , , - javascript - 如何响应ajax搜索输出查看~laravel
- angular - 如何在angular6中的ng-select中设置默认值?
- html - 为什么即使在本地工作,我的自定义字体也无法加载?
- php - 没有这样的 Token-StripeError
- php - PHP sqlsrv_fetch_array with "while loop" crosses the first row
- java - Stream.findFirst 与 Optional.of 不同?
- c# - 尝试在基抽象类中使用标记为内部的接口注入受保护的构造函数时出现不一致的可访问性错误
- tsql - pyspark中的窗口函数(滞后,领先)实现?
- pthreads - 线程再次遇到thread_create语句会不会重新启动?