database - session 和数据库的 oracle db 时区， session 区域的更改不起作用

Question

我正在尝试让数据库以与机场相同的方式显示日期。例如，如果您在得克萨斯州并且需要搭乘航类前往东海岸，机场侯爵将显示您本地时间的起飞时间和降落时间。因此，例如，从达拉斯飞往纽约的航类将显示该地区的本地时间。

Marquis in Dallas :     Takeoff time : 8AM  Landing time: 10AM  
Marquis in New York:    Takeoff time : 9AM  Landing time: 11AM

为了做到这一点，我认为数据库会以 UTC 格式存储时间。我知道 TIMESTAMP 没有与之关联的区域 - 但是 - 它确实允许将时间保存到数据库 与区域 附加到它 - 所以 - 我的想法是某种计算会已执行将其转换为 UTC。但是，根据我下面的小测试，这似乎并没有发生。无论我将 SESSION TIME ZONE 设置为什么，日期都保持不变。

TIA

SQL> create table toast ( t timestamp );
Table created.


SQL> insert into toast values ( TIMESTAMP '2019-09-23 16:03:11 US/Eastern');
1 row created.


SQL> select dbtimezone from dual;
DBT
---
UTC

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-04:00


SQL> select * from toast;
T
---------------------------------------------------------------------------
23-SEP-19 04.03.11.000000 PM

在 session 中更改时区仍然得到相同的日期

SQL> alter session set time_zone = 'America/Chicago';
Session altered.

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
America/Chicago

SQL> select * from toast;
T
---------------------------------------------------------------------------
23-SEP-19 04.03.11.000000 PM

再次更改，结果相同

SQL> alter session set time_zone = 'Pacific/Auckland';
Session altered.


SQL> select * from toast;
T
---------------------------------------------------------------------------
23-SEP-19 04.03.11.000000 PM

改为使用 hours 更改它，得到相同的结果

SQL> SQL> alter session set time_zone = '-3:00';
Session altered.


SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-03:00


SQL> select * from toast;
T
---------------------------------------------------------------------------
23-SEP-19 04.03.11.000000 PM

更新非常感谢@Alex Poole 的详细回复!

我曾使用 Hibernate、Java 和 Oracle，但在使用 Hibernate 保存基于时间的数据时遇到了一些问题(关于这部分，我在这里看到了这篇使用 JAVA Calendar 类格式化解决方案的帖子)。文章在这里:How To Handle Oracle TimeStamp with TimeZone from Java我也看过你之前提到的关于“长篇大论”的论文(以及其他文章)。他们似乎不鼓励使用 TIMESTAMP WITH LOCAL TIMEZONE。只是因为这个信息，我有点想完全坚持使用 TIMESTAMP :-) 但是，还有 TIMESTAMP WITH TIMEZONE 的选项。

你对这个Oracle类型的使用有什么想法吗？

Answer 1

您误解了数据类型。正如您所指出的，时间戳不存储时区，但它也不允许您“将时间保存到附加了区域的数据库”。

当你这样做时:

insert into toast values ( TIMESTAMP '2019-09-23 16:03:11 US/Eastern');

您正在将文字值隐式转换为普通时间戳，就像在做:

insert into toast values ( cast (TIMESTAMP '2019-09-23 16:03:11 US/Eastern' as timestamp ) );

原始区域信息未保留或不可用。没有转换(到 UTC 或其他任何方式)，原始时区信息只是被丢弃。

select TIMESTAMP '2019-09-23 16:03:11 US/Eastern',
  cast (TIMESTAMP '2019-09-23 16:03:11 US/Eastern' as timestamp )
from dual;

TIMESTAMP'2019-09-2316:03:11US/EASTERN' CAST(TIMESTAMP'2019-09-2316:
--------------------------------------- ----------------------------
23-SEP-19 16.03.11.000000000 US/EASTERN 23-SEP-19 16.03.11.000000000

您的时间戳文字的原始值显示时区； cast 值没有时区信息。

如您所见，更改 session 时区对普通 timestamp 没有影响，因为时区信息没有任何影响。您必须将数据类型设置为 timestamp with time zone 或 timestamp with local time zone 才能产生任何影响。

在您的情况下，您最终将处理不同时区的两个值，仅使用 session 时区并不能真正帮助您。您可以存储出发/到达机场的时区感知时间:

create table toast ( depart timestamp with time zone,
  arrive timestamp with time zone);

insert into toast ( depart, arrive )
values ( TIMESTAMP '2019-09-23 08:00:00 US/Central',
  TIMESTAMP '2019-09-23 11:00:00 US/Eastern' );

alter session set time_zone = 'UTC';

Session altered.

select to_char(depart, 'HH24:MI TZR') as depart,
  to_char(arrive, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
08:00 US/CENTRAL                       11:00 US/EASTERN                      

然后调整到本地机场/显示时区 with datetime expressions ，或者明确指定区域:

select to_char(depart at time zone 'US/Central', 'HH24:MI TZR') as depart,
  to_char(arrive at time zone 'US/Central', 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
08:00 US/CENTRAL                       10:00 US/CENTRAL                      

select to_char(depart at time zone 'US/Eastern', 'HH24:MI TZR') as depart,
  to_char(arrive at time zone 'US/Eastern', 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
09:00 US/EASTERN                       11:00 US/EASTERN                      

或者如果您确信这是正确的，则通过本地 session 时区:

alter session set time_zone = 'US/Central';

select to_char(depart at local, 'HH24:MI TZR') as depart,
  to_char(arrive at local, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
08:00 US/CENTRAL                       10:00 US/CENTRAL                      

alter session set time_zone = 'US/Eastern';

select to_char(depart at local, 'HH24:MI TZR') as depart,
  to_char(arrive at local, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
09:00 US/EASTERN                       11:00 US/EASTERN                      

虽然将时间存储为 UTC 可能更好，如果您愿意，仍然可以在普通时间戳中完成 - 所以一切都假设存储的值始终是 UTC - 并将原始时间手动或使用 sys_extract_utc() 转换为 UTC :

create table toast ( depart timestamp, arrive timestamp);

insert into toast ( depart, arrive )
values ( sys_extract_utc ( TIMESTAMP '2019-09-23 08:00:00 US/Central' ),
  sys_extract_utc ( TIMESTAMP '2019-09-23 11:00:00 US/Eastern' ) );

...

alter session set time_zone = 'US/Eastern';

select to_char(from_tz( depart, 'UTC' ) at local, 'HH24:MI TZR') as depart,
  to_char(from_tz ( arrive, 'UTC' ) at local, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
09:00 US/EASTERN                       11:00 US/EASTERN                      

但包括该地区可能更安全:

create table toast ( depart timestamp with time zone,
  arrive timestamp with time zone);

insert into toast ( depart, arrive )
values ( TIMESTAMP '2019-09-23 08:00:00 US/Central' at time zone 'UTC',
  TIMESTAMP '2019-09-23 11:00:00 US/Eastern' at time zone 'UTC' );

...

alter session set time_zone = 'US/Eastern';

select to_char(depart at local, 'HH24:MI TZR') as depart,
  to_char(arrive at local, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
09:00 US/EASTERN                       11:00 US/EASTERN                      

但是，如果您使用 timestamp with local time zone，则无论您如何转换输入时间，您都可以更简单地充分利用两者:

create table toast ( depart timestamp with local time zone,
  arrive timestamp with local time zone);

insert into toast ( depart, arrive )
values ( TIMESTAMP '2019-09-23 08:00:00 US/Central',
  TIMESTAMP '2019-09-23 11:00:00 US/Eastern' at time zone 'UTC' );

alter session set time_zone = 'UTC';

select to_char(depart, 'HH24:MI TZR') as depart,
  to_char(arrive, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
13:00 UTC                              15:00 UTC                             

alter session set time_zone = 'US/Central';

select to_char(depart, 'HH24:MI TZR') as depart,
  to_char(arrive, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
08:00 US/CENTRAL                       10:00 US/CENTRAL                      

alter session set time_zone = 'US/Eastern';

select to_char(depart, 'HH24:MI TZR') as depart,
  to_char(arrive, 'HH24:MI TZR') as arrive
from toast;

DEPART                                 ARRIVE                                
-------------------------------------- --------------------------------------
09:00 US/EASTERN                       11:00 US/EASTERN                      

(另请阅读 Tony’s Tirade against TIMESTAMP WITH TIME ZONE，了解有关数据类型选项的更多背景信息。)