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

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