关于ORACLE时区问题 ----------为什么我的JOB总不是在我想执行的时候跑

 我们的成功总是站在巨人的肩膀上我，先转了别人经验，最后再总结自己的实验结果，学习才能有进步！

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

 

 

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 Size                   454424 bytes
Variable Size              62914560 bytes
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 Size                   453992
bytes

Variable Size             109051904
bytes

Database Buffers           20971520
bytes

Redo Buffers                 667648

bytes

Database mounted.
Database opened.
SQL> select sysdate from dual;

SYSDATE
---------
02-AUG-06

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:32:59              <- 还是之前的时间

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 11.35.05.171000 PM +09:00          <- 时间正确

 

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

 

 

alter system set fixed_date='2005-04-04-11-00-00'

 

this fixed_date is normally used, in oracle, for dubugging purpose.

 

once finishing it, you can set it back:

 

alter system set fixed_date=none

 

    Eygle 的关于这个参数的相关文章：Why sysdate is fixed

    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;
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;
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 的时区

总结：

可以通过这个SQL来查看系统时区与系统时间的差异，以systimestamp里的时区为准

select sysdate from dual

union all

select current_date from dual

union all

select systimestamp from dual;