Oracle TIMESTAMP WITH TIME ZONE类型

来源：互联网发布：无间道剧情全解析知乎编辑：程序博客网时间：2024/05/18 15:27

Oracle TIMESTAMP WITH TIME ZONE类型

TIMESTAMP WITH TIME ZONE类型

Oracle定义的语法:
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

该类型在TIMESTAMP类型的基础上增加了时区的信息. 注意时区时差(TZH)的范围是"-12 ~ 13", fractional_seconds_precision的使用与TIMESTAMP类型是一致的. 在数据库中保存占13字节.
eg:

SQL> create table ff(a timestamp(8) with time zone);

Table created.

SQL> desc ff;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
A                                                  TIMESTAMP(8) WITH TIME ZONE

SQL> insert into ff values(TO_TIMESTAMP_TZ('2006-12-14 19:45:09.9003 -12:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));

1 row created.

SQL> select * from ff;

A
---------------------------------------------------------------------------
14-DEC-06 07.45.09.90030000 PM -12:00

SQL> insert into ff values(TO_TIMESTAMP_TZ('2006-12-14 19:45:09.9003 -12:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));

1 row created.

SQL> insert into ff values(TO_TIMESTAMP_TZ('2006-12-14 19:45:09.9003 -13:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'))
                                       *
ERROR at line 1:
ORA-01874: time zone hour must be between -12 and 13

错误解析：原因是-13，超过时区-12到13区间。

SQL> insert into ff values(TO_TIMESTAMP_TZ('2006-12-14 19:45:09.9003 13:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'))

1 row created.

--------------------------
注意缩写: "TZH" time zone hour, "TZM" time zone minute