关于Oracle Timezone的一点总结

本文转自：http://space.itpub.net/9765498/viewspace-539881
背景描述：如果需要支持一个国际化的应用，那么数据库端的国际化特性的支持也就显得尤其重要。Oracle中有很多特性支持国际化，如字符集、时区等等。如果相关参数设置不当，或者由于对相关特性不够了解，以至于在设计阶段没有考虑完全，那么肯定会对应用造成一定的损失。偶前不久也遇到了time zone相关的问题，所以在此结合遇到的问题，对时区问题作一个小小的总结。

1. 如何查看和修改数据库和session时区

Oracle中相关的时区大体可以分为两类：数据库时区和session时区。可以通过以下方式获得：

查看数据库时区信息：

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

查看session时区信息：

SQL> select sessiontimezone from dual;

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

Database的timezone可以在创建数据库的时候指定，如：

CREATE DATABASE db01
...
SET TIME_ZONE='+08:00';

或者在数据库创建之后通过alter database语句修改，但是只有重启数据库后有效：

ALTER DATABASE SET TIME_ZONE='+08:00';

session的timezone可以简单通过alter session语句修改：

ALTER SESSION SET TIME_ZONE='+08:00';

Note:Database Time Zone只和TIMESTAMP WITH LOCAL TIME ZONE数据类型相关！其实数据库timezone只是一个计算的标尺，TIMESTAMP WITH LOCAL TIME ZONE数据类型从客户端传入数据库后，转为数据库时区存入数据库。在需要进行相关计算的时候，Oracle先把时间转换为标准时间(UTC)，完成计算后再把结果转换为数据库时区的时间保存到数据库。关于TIMESTAMP WITH LOCAL TIME ZONE数据类型的详细信息，请参考随后相关部分:)

2. 时区相关的数据类型

和时区相关的数据类型主要有：DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE。粗略介绍如下：

DATE：存储日期和时间信息，精确到秒。

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select to_date('2009-01-12 13:24:33','YYYY-MM-DD HH24:MI:SS') from dual;

TO_DATE('2009-01-12
-------------------
2009-01-12 13:24:33

TIMESTAMP：DATE类型的扩展，保留小数级别的秒，默认为小数点后６位。不保存时区和地区信息。

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07.21.37.984000 PM

TIMESTAMP WITH TIME ZONE：存储带时区信息的TIMESTAMP(以和UTC时间差或者地区信息的形式保存)。形式大致为：

TIMESTAMP '2009-01-12 8:00:00 +8:00'

TIMESTAMP WITH LOCAL TIME ZONE：另一种不同类型的TIMESTAMP，和TIMESTAMP WITH TIME ZONE类型的区别在于：数据库不保存时区相关信息，而是把客户端输入的时间转换为基于database timezone的时间后存入数据库(这也就是database tmiezone设置的意义所在，作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺)。当用户请求此类型信息时，Oracle把数据转换为用户session的时区时间返回给用户。所以Oracle建议把database timezone设置为标准时间UTC，这样可以节省每次转换所需要的开销，提高性能。

下面是针对以上几种类型所做的实验：

操作DATE类型数据：

SQL> INSERT INTO table_dt VALUES(1,DATE '2009-01-01');

1 row created.

SQL> INSERT INTO table_dt VALUES(2,TIMESTAMP '2009-01-01 00:00:00 Asia/Hong_Kong');

1 row created.

SQL>  INSERT INTO table_dt VALUES(3,TO_DATE('01-JAN-2009','DD-MON-YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_dt;

      C_ID C_DT
---------- -------------------
         1 2009-01-01 00:00:00
         2 2009-01-01 00:00:00
         3 2009-01-01 00:00:00

操作TIMESTAMP数据类型：

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

Session altered.

SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);

Table created.

SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2009 2:00:00');

1 row created.

SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2009-01-01 2:00:00 -08:00');

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 120
SQL> select * from table_ts;

      C_ID C_TS
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 02:00:00.000000
         2 01-JAN-09 02:00:00.000000
         3 01-JAN-09 02:00:00.000000

Note: 第三条数据的时区信息丢失！

操作TIMESTAMP WITH TIME ZONE数据类型：

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';

Session altered.

SQL> ALTER SESSION SET TIME_ZONE='-7:00';

Session altered.

SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);

Table created.

SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2009 2:00:00 AM -07:00');

1 row created.

SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2009-01-01 2:00:00 -8:00');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_tstz;

      C_ID C_TSTZ
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 02:00:00.000000 AM -07:00
         2 01-JAN-09 02:00:00.000000 AM -07:00
         3 01-JAN-09 02:00:00.000000 AM -08:00
Note: 第三条数据保存了时区信息！ 可以和上一个例子TIMESTAMP类型做一个对比。

操作TIMESTAMP WITH LOCAL TIME ZONE数据类型：

SQL> ALTER SESSION SET TIME_ZONE='-07:00';

Session altered.

SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2009 2:00:00');

1 row created.

SQL>  INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2009-01-01 2:00:00 -08:00');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_tsltz;

      C_ID C_TSLTZ
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 02:00:00.000000
         2 01-JAN-09 02:00:00.000000
         3 01-JAN-09 03:00:00.000000

Note:插入的第三条数据指定为UTC-8时区的时间，然后存入数据库后按照database timezone的时间保存，最后在客户端请求的时候，转换为客户端时区的时间(UTC-7)返回！可以参考以下简单实验：

SQL> ALTER SESSION SET TIME_ZONE='-05:00';

Session altered.

SQL> select * from table_tsltz;

      C_ID C_TSLTZ
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 04:00:00.000000
         2 01-JAN-09 04:00:00.000000
         3 01-JAN-09 05:00:00.000000

可以看出，当客户端时区改为UTC-5的时候，TIMESTAMP WITH LOCAL TIME ZONE数据类型的返回信息是会相应改变的。

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

当你不需要保存时区/地区信息的时候，选择使用TIMESTAMP数据类型，因为它一般需要7-11bytes的存储空间，可以节省空间。

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

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

3. 时区相关的几个函数

DBTIMEZONE -- Returns the value of the database time zone. The value is a time zone offset or a time zone region name.

SESSIONTIMEZONE -- Returns the value of the current session's time zone.

CURRENT_DATE -- Returns the current date in the session time zone in a value in the Gregorian calendar, of the DATE datatype.

CURRENT_TIMESTAMP -- Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE value.

SYSDATE -- Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.

SYSTIMESTAMP -- Returns the system date, including fractional seconds and time zone of the system on which the database resides.

Note:SYSDATE和SYSTIMESTAMP的返回信息是数据库所在操作系统的信息，和当前session的时区无关！

例：

数据库时区为+08:00，当前session时区为-05:00时：

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2009-01-12 06:18:24

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 06:18:36.625000 AM -05:00

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2009-01-12 19:18:42

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07:18:52.921000 PM +08:00

SQL>

把当前session时区改为+09:00以后：

SQL> alter session set time_zone='+09:00';

Session altered.

SQL>
SQL> select dbtimezone from dual;

DBTIME
------
+08:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+09:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2009-01-12 20:19:54

SQL>
SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 08:20:07.218000 PM +09:00

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2009-01-12 19:20:24

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07:20:30.921000 PM +08:00

SQL>

从以上例子可以看出，SYSDATE和SYSTIMESTAMP的返回结果是不随SESSION时区的改变而改变的，其实从函数的命名就能看出(一组是system的，一组是current的~):D

总结：由于这次case涉及到的东西就那么多，因此总结起来也没有面面俱到，所有东西都包括。这里只是简单的总结了怎么查看和修改数据库/session时区，相关的data types和functions。还有诸如Interval Datatypes(存储的是时间间隔), Daylight Saving Time(夏令时，我到现在还不是很清楚~)以及其他functions，parameters等等都没有涉及。

关于time zone的系统介绍，请参考Oracle Database Globalization Support Guide, Chapter 4。还有其他的官方文档和metalink都可以作为参考。

PS:小小case一则~

应用层用户发现sysdate信息不对，本应为+09:00的时间，却显示为-05:00时区的时间，要求修改数据库timezone。其实sysdate返回信息和数据库timezone设置无关，遂去查看操作系统信息。发现果然是操作系统层设置就存在问题。但当时的问题是操作系统又不能随便重启，问题变得很棘手！

后来经过同事建议，设置了操作系的session信息：setenv TZ Japan。然后重启了listener和database。之后所有经过listener连接到数据库的用户select sysdate from dual;的结果都是正确的信息，而没有通过listener连接的用户得到的则还是错误的信息，因为操作系统本身的时区并没有更新。据说在session级别设置好时区信息后，只要重启listener就足够了，个人没有试过，有兴趣的可以尝试下~

问题的根本解决方法还是要修改操作系统的时区设置，但有个临时的解决方案也是不错的了:)

还有一些关于字符集、地区等国际化特性的总结，以后整理下慢慢都发出来吧...:)