Oracle timezone的相关知识

目前我工作中生产环境都是面向国际化的应用，会遇到有些关于时区的问题，在此做下总结。

oracle中的时区大体分为两类：数据库时区、session 时区。我们可以通过以下方式来查看：

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-08:00

数据库时区可以在建库的时候指定，或者在数据库建成后使用 alter database  set time_zone='+8:00' 进行修改，修改后重启库才能生效。
session时区使用 alter session set time_zone=‘+8:00’ 进行修改。

在这里我们不得不提的是和时区相关的数据类型，因为有这些数据类型，时区的设置才有意义。
与时区相关的数据类型：DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE 等

DATE：存储的是日期+时间，精确到秒级，不保存时区和地区信息。
SQL> select sysdate from dual;

SYSDATE
-------------------
2014-02-12 01:12:18

TIMESTAMP:DATE类型的扩展，保留小数级别的秒，默认为小数点后６位，不保存时区和地区信息。
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-FEB-14 01.14.12.945256 AM

SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF';       ----修改TIMESTAMP数据类型的格式

Session altered.

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
2014-02-12 01:28:31.652888

TIMESTAMP WITH TIME ZONE:存储带时区信息的TIMESTAMP，此类型的数据在保存到数据库时带有当前客户端的session timezone，无论在什么时区查看这些数据的时候，数据都不会随时区的不同而变化。

SQL> create table t1 (id number,time timestamp with time zone);         ----创建t1表，其中time 列的数据类型是timestamp with time zone

Table created.

SQL> select sessiontimezone from dual;                                                 -----当前客户端的session timezone 是 -8:00
 
SESSIONTIMEZONE
---------------------------------------------------------------------------
-08:00

SQL> insert into t1 values(1,timestamp '2014-02-12 02:00:00');          ----向t1表中插入一条数据  
1 row created.

SQL> select * from t1;                                              ----查看t1表，其中 time列带时区显示，并且时区为数据被插入时的session timezone

        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

SQL> alter session set time_zone='-6:00';                                    ----修改当前客户端的session timezone为 -6:00

Session altered.

SQL> select * from t1;                                                                ----再次查看t1表，其中time列数据无变化。

        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

TIMESTAMP WITH LOCAL TIME ZONE：另一种不同类型的TIMESTAMP，数据库不保存时区相关信息，而是把客户端输入的时间转换为基于
database timezone的时间后存入数据库，(这也就是database tmiezone设置的意义所在，作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺)。
当用户请求此类型信息时，Oracle把数据转换为用户session的时区时间返回给用户。

SQL> create table t2(id number,time timestamp with local time zone);        ----- 创建t2表，其中time列为TIMESTAMP WITH LOCAL TIME ZONE

Table created.

SQL> insert into t2 values(1,timestamp '2014-02-12 02:10:00 -8:00');      

1 row created.                                        -----在t2表插入数据指定时区为-8:00，实际在保存到数据库时转化为基于database timezone 的时间保存

SQL> select sessiontimezone from dual;              -----当前客户端的session timezone 为 -6:00

SESSIONTIMEZONE
---------------------------------------------------------------------------
-06:00

SQL> select * from t2;                      -----查看时oracle将数据转换成当前客户端session timezone的时间

        ID      TIME
----------   ---------------------------------------------------------------------------
         1        2014-02-12 04:10:00.000000

在了解了相关数据类型后，那么我们该如何在它们之间做出选择呢？

不需要保存时区/地区信息的时候，选择使用TIMESTAMP数据类型

需要保存时区/地区信息的时候，请选择使用TIMESTAMP WITH TIME ZONE数据类型。比如一个跨国银行业务应用系统，需要精确纪录每一笔交易
的时间和地点(时区)，在这种情况下就需要纪录时区相关信息。

当我们并不关心操作发生的具体地点，而只是关心操作是在你当前时区的几点发生的时候，选择使用TIMESTAMP WITH LOCAL TIME ZONE。
比如一个全球统一的change control system。用户可能只关心某某操作是在我的时间几点发生的(比如中国用户看到的是北京时间8:00am，
而伦敦的用户看到的是0:00am)。记住，此类行不保存时区/地区信息，因此如果需要保存相关信息的要慎重！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29457434/viewspace-1080444/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29457434/viewspace-1080444/