在本教程中,您将学习如何使用Oracle TIMESTAMP WITH TIME ZONE数据类型来存储包含时区的日期和时间数据。

带时区的Oracle TIMESTAMP简介

TIMESTAMP WITH TIME ZONE数据类型存储时间戳和时区数据。
时区数据可以是时区偏移,例如-07:00,它是本地时间与UTC时间或时区区域名称之间的差,例如:欧洲/伦敦

以下表达式指定了TIMESTAMP WITH TIME ZONE数据类型:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

在此表达式中,fractional_seconds_precision是一个可选参数,用于指定SECOND字段的小数部分中的位数。

fractional_seconds_precision的范围是0~9,默认值是6。

如果不考虑时区数据,Oracle认为两个TIMESTAMP WITH TIME ZONE值相同(如果它们表示UTC中的相同值)。

例如,下面的TIMESTAMP WITH TIME ZONE值是相等的:

TIMESTAMP '2017-08-09 07:00:00 -7:00'
TIMESTAMP '2017-08-09 09:00:00 -5:00'

以下语句证明了这一点:

SELECT
  DECODE( TIMESTAMP '1999-01-15 8:00:00 -8:00', 
          TIMESTAMP '1999-01-15 11:00:00 -5:00', 
          'Equal', 
          'Not Equal' )
FROM
  dual;

注意到DECODE()函数返回第三个参数,如果第一个参数等于第二个参数,否则返回第四个参数。

对于跨时区存储的应用程序(例如事件调度,银行业务和预订应用程序等),使用TIMESTAMP WITH TIME ZONE数据类型是一种很好的做法。

1. TIMESTAMP WITH TIME ZONE字面值

以下格式指定了TIMESTAMP WITH TIME ZONE字面值:

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'

例如,

TIMESTAMP '2017-08-10 10:30:20.15 -07:00'

除了时区偏移之外,还可以使用时区的名称,如下所示:

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZR'

例如,

TIMESTAMP '2017-08-10 10:30:20.15 US/Pacific'

对于某些有夏时制和标准时间的地区,应该明确包含TZD格式元素,以避免时间从一个切换到另一个时出现边界不明确的情况。

例如,美国/太平洋标准时间PST和美国/太平洋夏令时PDT。 以下值确保返回夏令时值:

TIMESTAMP '2017-08-10 10:30:20.15 US/Pacific PDT'

如果不指定PDT,则默认情况下,如果ERROR_ON_OVERLAP_TIME会话参数设置为FALSE,则Oracle将其解释为标准时间;如果ERROR_ON_OVERLAP_TIME设置为TRUE,则会发出错误。

2. TIMESTAMP WITH TIME ZONE值的默认格式

默认情况下,TIMESTAMP WITH TIME ZONE数据类型的日期格式由NLS_TIMESTAMP_TZ_FORMAT参数控制。

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_TIMESTAMP_TZ_FORMAT';

Oracle数据库中的默认值是:

VALUE
----------------------------------------------------------------
DD-MON-RR HH.MI.SSXFF AM TZR

如果要更改此默认格式,请使用ALTER SESSION SET语句。

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'format';

有关如何构建的详细信息,请参阅Oracle日期格式。

3. Oracle TIMESTAMP WITH TIME ZONE示例

下面为了演示,创建一个名为logs的新表。

CREATE TABLE logs
  (
    log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
    log_message VARCHAR2(255) NOT NULL,
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL,  
    PRIMARY KEY(log_id)
  );

created_at列的数据类型是TIMESTAMP WITH TIME ZONE。 接下来,我们将演示将数据插入此列的各种方法。

首先,来看看:

SELECT SESSIONTIMEZONE FROM dual;

执行上面查询语句,得到以下结果(目前,它被设置为) 

-07:00

以下语句将TIMESTAMP值插入到created_at列中。

INSERT INTO logs(log_message, created_at)
VALUES('Insert a timestamp',TIMESTAMP '2017-08-08 2:00:00');

由于时间戳值没有指定时区数据,但Oracle在存储它之前追加了会话时区。

SELECT * FROM logs;

查询结果如下图所示 -

以下语句将字符串格式的日期时间插入到created_at列中 -

INSERT INTO logs(log_message, created_at)
VALUES('Insert a timestamp with timezone as a character string','08-AUG-2017 2:00:00 PM -05:00');

在这种情况下,Oracle将字符串转换为相应的TIMESTAMP WITH TIME ZONE值。
看到插入到表中的以下值:

以下语句将TIMESTAMP WITH TIME ZONE字面值插入到created_at列中:

INSERT INTO logs(log_message, created_at)
VALUES('Insert a timestamp with time zone literal',TIMESTAMP '2017-08-08 2:00:00 -08:00');

这是如何存储在表中的数据:

以下语句使用CURRENT_TIMESTAMP函数的结果来插入:

INSERT INTO logs(log_message, created_at)
VALUES('Use current_timestamp function',CURRENT_TIMESTAMP);

Oracle将带有会话时区的当前时间戳插入到表中:

 您阅读本篇文章共花了：