Oracle的时区

从Oracle 9i开始多了3个关于时间的数据类型：

TIMESTAMP [(precision)]

TIMESTAMP [(precision)] WITH TIME ZONE

TIMESTAMP [(precision)] WITH LOCAL TIME ZONE

其中TIMESTAMP [(precision)] WITH TIME ZONE保存了时区信息。

 

1. Oracle的时区设置

Oracle的时区可以分为两种，一种是数据库的时区，一种是 session时区，session时区就是客户端连接时的时区。

数据库的时区在创建数据库时可以通过在 create database语句中加上

SET TIME_ZONE = ' { { + | - } hh : mi | time_zone_region } '来指定，如果不指定，默认是按照数据库所在的操作系统时区来设定的。创建之后，可以通过 alter database来修改。可以通过查询 V$TIMEZONE_NAMES动态视图来获得所有支持的时区值。修改之后，需要重启数据库才能生效。经常有人会碰到无法修改的情况：

 

SQL> alter database set time_zone='+06:00';
alter database set time_zone='+06:00'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

 

TOM对此问题有过解释，TIME_ZONE的设定主要是为了WITH LOCAL TIME ZONE，当session的时区和数据库的时区不同时，oracle根据时区的差距转换到数据库的时间，再保存到数据库的WITH LOCAL TIME ZONE类型中，他是不保存时区的，所以需要TIME_ZONE来进行各种时区之间时间的转换（WITH TIME ZONE类型保存了原始的时区，所以不需要TIME_ZONE的设置也可以进行各种时区之间的转换）。但数据库中一旦有了该类型，就不能通过 alter database修改时区了，会得到上面的错误，可以通过下面的语句获得所有包含该类型的表，将他们删除之后，再修改。

 

selectu.name || '.' ||o.name || '.' ||c.name TSLTZcolumn
from sys.obj$ o, sys.col$ c, sys.user$ u
where c.type# = 231
and o.obj# = c.obj#
and u.user# = o.owner#;

(一般查询后的结果为：OE.ORDERS.ORDER_DATE，指的是OE用户下的ORDERS表的ORDER_DATE字段使用了时区的信息：WITH LOCAL TIME ZONE，将此信息去掉就可以再修改了，修改好了之后需要重启数据库才能生效)

Session的时区是根据客户端的时区来决定的，当然连接以后也可以通过alter session来改变。WITH LOCAL TIME ZONE类型会根据 TIME_ZONE的设置，自动把时间转换为session所在时区的时间显示出来，而WITH TIME ZONE因为保存了时区，不需要根据 TIME_ZONE的设置来转换。

 

2.查看时区

可以分别使用 SESSIONTIMEZONE / DBTIMEZONE内建函数查看 session和数据库时区：

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------
Australia/Sydney

另外可以用 TZ_OFFSET查询某时区和 UTC之间的差值。

 格式：

TZ_OFFSET ( { 'time_zone_name'
| '{ + | - } hh : mi'
| SESSIONTIMEZONE
| DBTMEZONE }
)

 

SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

TZ_OFFS
-------
-05:00

SQL> SELECT TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFS
-------
+00:00

其中 time_zone_name也可以从 V$TIMEZONE_NAMES获得。

 

3. 修改session的时区及几个内建时间函数的比较

sysdate：返回数据库的时间，默认是操作系统的时间。date类型，没有时区信息。

systimestamp：返回数据库的时间及，默认是操作系统的时间。返回TIMESTAMP WITH TIME ZONE类型，有时区信息。

current_date：返回session的时间。

current_timestamp：返回session时间以及时区信息。

 

修改session的时区：

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

或者 alter session set time_zone='Asia/Chongqing';

修改数据库时区，重启数据库才能生效:
alter database set time_zone = '+10:00';

 

实验：

--查看数据库和会话的当前时区：

SQL> col SESSIONTIMEZONE for a30

SQL>  select dbtimezone,sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
------ ------------------------------
+00:00 +00:00

--查看数据库和会话的当前时间：

SQL> select sysdate,current_date from dual;

SYSDATE   CURRENT_D
--------- ---------
19-NOV-13 19-NOV-13

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

Session altered.

SQL> select sysdate,current_date from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
2013-11-19 10:45:54 2013-11-19 10:45:54

SQL>

 

--修改当前会话的时区

SQL> alter session set time_zone ='Australia/Sydney';

Session altered.

 

--再来查看数据库和会话的时间和时区，发现数据库不变，会话的时区和时间已修改

SQL> select sysdate,current_date from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
2013-11-19 10:47:42 2013-11-19 13:47:42

SQL>  select dbtimezone,sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
------ ------------------------------
+00:00 Australia/Sydney

 

 

---整理自网络----