ORACLE 时区

商业和数据库很多时候必须跨时区工作，从9i开始，oracle环境开始有了时区意识，通过指定数据库的时区和使用TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE数据类型来实现该功能。

 

TIMESTAMP WITH TIME ZONE不会存储数据库时区，但是有一个指示用来说明该时间所使用的时区。TIMESTAMP WITH LOCAL TIME ZONE会同时存储数据库时区，并且会根据查询客户端的时区进行相应的转换。

 

数据库时区可以在创建时在create database命令中设置，也可以使用alter database set TIME_ZONE=...来修改。如果没有特别的指定，默认的，数据库将遵从主机操作系统时区设置，或者取自环境变量ORA_STDZ。所有支持的时区记录在V$TIMEZONE_NAMES动态性能表中。时区有三种表示方法，全名、缩写和相对于标准时间（格林威治时间）的固定偏移,比如标准时间相应的三种表示方法分别为：Etc/Greenwich、GMT和+00:00。

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';
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 修改时区了，会得到上面的错误，可以通过下面的语句获得所有包含该类型的表，将他们删除之后，再修改。

 

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 和数据库时区

 

3. 几个内建时间函数的比较

sysdate/systimestamp 都是返回数据库的时间并且使用数据库的时区，他们返回的是操作系统的时间。sysdate 返回的是 date 类型，没有时区信息，操作系统上是什么时间就返回什么时间；systimestamp 返回 TIMESTAMP WITH TIME ZONE 类型，有时区信息。

 

另外，有个初始化参数 fixed_date，可以设置 sysdate 返回指定的时间

 

文档上的解释如下
FIXED_DATE

Parameter type    String
 
Syntax                  FIXED_DATE = YYYY-MM-DD-HH24:MI:SS (or the default Oracle date format)
 
Default value        There is no default value.
 
Parameter class    Dynamic: ALTER SYSTEM
 
FIXED_DATE enables you to set a constant date that SYSDATE will always return instead of the current date. This parameter is useful primarily for testing. The value can be in the format shown above or in the default Oracle date format, without a time.

 

current_timestamp/current_date 也会返回数据库的时间，但转换为 session 的时区进行显示，可以使用 alter session set time_zone 改变 session 时区。

 

4. 四个日期时间类型的实验

 

SQL> 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> alter session set nls_date_format ='yyyy-dd-mm hh24:mi:ss';

Session altered.

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 Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

修改了客户端操作系统的时区

C:/Documents and Settings/Administrator>sqlplus sky/xxxx

SQL*Plus: Release 9.2.0.3.0 - Production on Wed Aug 2 23:28:01 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-08-02 22:28:49        <-数据库没有重启，时间依然是修改前的

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 11.29.33.609000 PM +09:00  <- 这里却已经改变了，有时区信息
SQL> select * from tztest;

A
-------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
D
---------------------------------------------------------------------------
2006-08-02 22:25:59                       <- 没变
02-AUG-06 10.25.59 PM                  <- 没变
02-AUG-06 10.25.59 PM +08:00      <- 保存时区信息
02-AUG-06 11.25.59 PM                  <-自动转换为 session 的时区