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 来修改。其中 time_zone_region 参数可以通过查询 V$TIMEZONE_NAMES 动态视图来获得所有支持的值。修改之后，需要重启数据库才能生效。经常有人会碰到无法修改的情况：

　　SQL>>　　alter database set time_zone='+06:00'
　　*
　　ERROR at line 1:

　　ORA-02231: missing or invalid option to>　　TOM 对此问题有过解释，TIME_ZONE 的设定主要是为了 WITH LOCAL TIME ZONE，当 session 的时区和数据库的时区不同时，oracle 根据时区的差距转换到数据库的时间，再保存到数据库的 WITH LOCAL TIME ZONE 类型中，他是不保存时区的，所以需要 TIME_ZONE 来进行各种时区之间时间的转换（WITH TIME ZONE 类型保存了原始的时区，所以不需要 TIME_ZONE 的设置也可以进行各种时区之间的转换）。但数据库中一旦有了该类型，就不能通过 alter database 修改时区了，会得到上面的错误，可以通过下面的语句获得所有包含该类型的表，将他们删除之后，再修改。
　　select u.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 和数据库时区：
　　SYS@SKYDB> select dbtimezone from dual;
　　DBTIME
　　------
　　+08:00
　　SYS@SKYDB> select sessiontimezone from dual;
　　SESSIONTIMEZONE
　　---------------------------------------------
　　+09:00
　　另外可以用 TZ_OFFSET 查询某时区和 UTC 之间的差值。
　　TZ_OFFSET ( { 'time_zone_name'
　　| '{ + | - } hh : mi'
　　| SESSIONTIMEZONE
　　| DBTMEZONE }
　　)
　　SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
　　TZ_OFFS
　　-------
　　-04:00
　　SELECT TZ_OFFSET(DBTIMEZONE) FROM DUAL;
　　TZ_OFFSET(DBTI
　　--------------
　　+08:00
　　其中 time_zone_name 也可以从 V$TIMEZONE_NAMES 获得。
　　3. 几个内建时间函数的比较
　　sysdate/systimestamp 都是返回数据库的时间并且使用数据库的时区，他们返回的是操作系统的时间。sysdate 返回的是 date 类型，没有时区信息，操作系统上是什么时间就返回什么时间；systimestamp 返回 TIMESTAMP WITH TIME ZONE 类新，有时区信息：
　　SYS@SKYDB> select sysdate from dual;
　　SYSDATE
　　-------------------
　　2006-08-03 10:01:31
　　SYS@SKYDB> select systimestamp from dual;
　　SYSTIMESTAMP
　　-----------------------------------------------
　　03-AUG-06 10.02.21.093000 AM +08:00
　　SYS@SKYDB> shutdown immediate
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　修改操作系统时区为 +02:00
　　SYS@SKYDB> startup
　　ORACLE instance started.
　　Total System Global Area 89202456 bytes

　　Fixed>
　　Variable>　　Database Buffers 25165824 bytes
　　Redo Buffers 667648 bytes
　　Database mounted.
　　Database opened.
　　SYS@SKYDB> select sysdate from dual;
　　SYSDATE
　　-------------------
　　2006-08-03 04:03:37
　　SYS@SKYDB> select systimestamp from dual;
　　SYSTIMESTAMP
　　----------------------------------------------
　　03-AUG-06 04.04.15.687000 AM +02:00
　　注：这是我单位机子上实验的结果，由于建了多个数据库，不知道为什么不能通过 ipc 来连接本地数据了，登陆时使用 sqlplus "/@skydb as sysdba"，也就是使用了监听器来连接，但在家里做相同的实验，通过 ipc 连接 sqlplus "/as sysdba"，修改时区后，sysdate 依然显示修改前的时间，而 systimestamp 却正确，不知道是什么原因：
　　SQL> select sysdate from dual;
　　SYSDATE
　　-------------------
　　2006-02-08 22:21:40
　　SQL> select systimestamp from dual;
　　SYSTIMESTAMP
　　---------------------------------------------------------------------------
　　02-AUG-06 10.22.38.578000 PM +08:00
　　SQL> shutdown immediate
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　修改时区为 +09:00
　　SQL> startup
　　ORACLE instance started.
　　Total System Global Area 131145064
　　bytes

　　Fixed>　　bytes

　　Variable>　　bytes
　　Database Buffers 20971520
　　bytes
　　Redo Buffers 667648
　　bytes
　　Database mounted.
　　Database opened.
　　SQL> select sysdate from dual;
　　SYSDATE
　　---------
　　02-AUG-06

　　SQL>>
　　Session>　　SQL> select sysdate from dual;
　　SYSDATE
　　-------------------
　　2006-08-02 22:32:59  select systimestamp from dual;
　　SYSTIMESTAMP
　　---------------------------------------------------------------------------
　　02-AUG-06 11.35.05.171000 PM +09:00  select dbtimezone from dual;
　　DBTIME
　　------
　　+06:00
　　SQL> select sessiontimezone from dual;
　　SESSIONTIMEZONE
　　---------------------------------------------------------------------------
　　+08:00
　　SQL> ed
　　Wrote file afiedt.buf
　　1 create table tztest(a date,
　　2 b timestamp(0),
　　3 c timestamp(0) with time zone,
　　4* d timestamp(0) with local time zone)
　　SQL> /
　　Table created.

　　SQL>>
　　Session>　　SQL> select sysdate from dual;
　　SYSDATE
　　-------------------
　　2006-02-08 22:21:40
　　SQL> select systimestamp from dual;
　　SYSTIMESTAMP
　　---------------------------------------------------------------------------
　　02-AUG-06 10.22.38.578000 PM +08:00
　　SQL> select current_date from dual;
　　CURRENT_DATE
　　-------------------
　　2006-02-08 22:23:50
　　SQL> select current_timestamp from dual;
　　CURRENT_TIMESTAMP
　　---------------------------------------------------------------------------
　　02-AUG-06 10.24.04.031000 PM +08:00
　　SQL> insert into tztest
　　2 values(sysdate,systimestamp,systimestamp,systimestamp);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL> select * from tztest;
　　A
　　-------------------
　　B
　　---------------------------------------------------------------------------
　　C
　　---------------------------------------------------------------------------
　　D
　　---------------------------------------------------------------------------
　　2006-02-08 22:25:59
　　02-AUG-06 10.25.59 PM
　　02-AUG-06 10.25.59 PM +08:00
　　02-AUG-06 10.25.59 PM
　　SQL> exit

　　Disconnected from Oracle9i Enterprise Edition>　　With the Partitioning, OLAP and Oracle Data Mining options

　　JServer>　　修改了客户端操作系统的时区
　　C:Documents and SettingsAdministrator>sqlplus sky/xxxx

　　SQL*Plus:>　　Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
　　Connected to:

　　Oracle9i Enterprise Edition>　　With the Partitioning, OLAP and Oracle Data Mining options

　　JServer>
　　SQL>>
　　Session>　　SQL> select sysdate from dual;
　　SYSDATE
　　-------------------
　　2006-08-02 22:28:49  select systimestamp from dual;
　　SYSTIMESTAMP
　　---------------------------------------------------------------------------
　　02-AUG-06 11.29.33.609000 PM +09:00  select * from tztest;
　　A
　　-------------------
　　B
　　---------------------------------------------------------------------------
　　C
　　---------------------------------------------------------------------------
　　D
　　---------------------------------------------------------------------------
　　2006-08-02 22:25:59