Oracle的时区问题

来源：互联网发布：西海岸新区知乎编辑：程序博客网时间：2024/04/29 17:23

Oracle 9i 开始多了 3 个关于时间的数据类型：TIMESTAMP [(precision)] TIMESTAMP[(precision)] WITH TIME ZONE TIMESTAMP [(precision)] WITH LOCALTIME ZONE，其中 TIMESTAMP [(precision)] WITH TIME ZONE保存了时区信息。-----(同时可以参考书籍OCP认证考试指南全册P423页这几个数据类型的设置的例子)例如：TIMESTAMP(n), TIMESTAMP (n) WITH TIME ZONE, TIMESTAMP (n)WITHLOCAL TIME ZONE,其中n为秒之后的小数点位数，例如n=9的时候，小数点位数为：timestamp'2008-01-011:0:1.000000001',

区别： TIMESTAMP (n) WITH TIME ZONE与TIMESTAMP (n)WITH LOCALTIMEZONE。前者是在什么时区下添加或者修改的数据，那么就会显示什么时区，后边带有更新的时区。后者是将用户在什么时区更新的时间转换到数据库本地时区，然后存储起来，当客户端用户需要检索的时候，那么就从存储的数据里头转换到用户的本地时区的时间，然后显示出来。

实验如下：建了表

SQL> create table t_timestamp(t1 date,t2timestamp(9),
t3 timestamp(9) with local time zone,t4 timestamp(9) with timezone);

设置数据库时区为+06:00
SQL> select dbtimezone from dual; +06:00

设置用户的会话时区为+08:00，假设是用户的本地时区。
SQL> select sessiontimezone from dual; +08:00

用户在本地时区+08:00的会话下增加一条记录，如下
SQL> insert into t_timestamp values
(to_date('2008-01-01 1:0:1','yyyy-mm-dd hh24:mi:ss'),
timestamp'2008-01-01 1:0:1.000000001',
timestamp'2008-01-01 1:0:1.000000001',
timestamp'2008-01-01 1:0:1.000000001');

用户在本地时区+08:00的会话下查询这条记录，如下

SQL> select t1,t2,t3,t4 from t_timestamp;

T1 T2 T3

2008/1/1 1:00:01 01-JAN-0801.00.01.000000001 AM 01-JAN-0801.00.01.000000001 AM

01-JAN-08 01.00.01.000000001 AM +08:00

然后模拟用户在不同的时区所检索的数据，例如在东12区，即：+12:00
SQL> alter session set time_zone='+12:00'
SQL> select sessiontimezone from dual; '+12:00'

SQL> select dbtimezone from dual;+06:00

再次查询，即：+12:00,查询的数据还是东八区用户新增的数据。注意看TIMESTAMP (n) WITHTIME ZONE与TIMESTAMP (n)WITH LOCAL TIME ZONE两列的值，TIMESTAMP (n) WITHTIME ZONE的列（T4列）还是东八区的时间，而TIMESTAMP (n)WITH LOCAL TIMEZONE列（T3列）已经将东八区的用户新增的时间转换为本地数据库时区时间，然后从数据库中读出再转换到东12区的时区时间显示给+12:00时区的用户
SQL> select * fromt_timestamp;

T1 T2 T3
2008/1/11:00:01 01-JAN-08 01.00.01.000000001AM 01-JAN-08 05.00.01.000000001

AM 01-JAN-08 01.00.01.000000001 AM +08:00

可以看到timestamp with local timezone与sessiontimezone有关。而timestamp withtime zone
与dbtimezone有关，CURRENT_TIMESTAMP 与时区设置有关，返回的秒是系统的，返回的日期和
时间是根据时区转换过的。即返回的就是timestamp with local timezone
sysdate返回的是系统的时间。systimestamp 返回的是timestamp类型
alter session set time_zone='UTC';
select to_char(sysdate,'YYYYMMDD HH24:MI:SS'),
CURRENT_TIMESTAMP ,systimestamp,TZ_OFFSET(sessiontimezone) fromdual;

Timestamp(precision)的precision是指timestamp的精度，取值0-9，默认为6，而timestamp
的精度达到billion（十亿）分之一秒，是以11个字节存储。
如果超过最大精度会报错：ORA-01861: 文字与格式字符串不匹配

SQL> commit;
SQL> select t2,dump(t2) from t_timestamp;
T2 DUMP(T2)
--------------------------------------
01-1月-0801.00.01.000000001上午 Typ=180 Len=11:120,108,1,1,2,1,2,0,0,0,1
01-1月-0801.00.01.000000002上午 Typ=180 Len=11:120,108,1,1,2,1,2,0,0,0,2
01-1月-0801.00.01.000000099上午 Typ=180 Len=11:120,108,1,1,2,1,2,0,0,0,99
01-1月-0801.00.01.000000255上午 Typ=180 Len=11:120,108,1,1,2,1,2,0,0,0,255

会发现date部分的存储就是date类型数据的存储格式,7个字节。而timestamp精度的存储是以无
符号整数的格式进行存储，而4个字节能达到billions的精度：255的4次幂。
SQL> select * from t_timestamp wheret3>'2008-01-01';
ORA-01843: 无效的月份，T3列是时间列，需要将字符串转换。下

SQL>select * from t_timestamp wheret3> to_date('2007-12-30','RRRR-MM-DD');
可以查出数据,说明查询Timestamp 类型需转换函数

2、

TIMESTAMP WITH TIMEZONE：存储带时区信息的TIMESTAMP(以和UTC时间差或者地区信息的形式保存)。形式大
致为：
TIMESTAMP '2009-01-12 8:00:00 +8:00'--带有时区标志的时间

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

操作TIMESTAMP WITH LOCAL TIME ZONE数据类型：
SQL> ALTER SESSION SET TIME_ZONE='-07:00';
SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltzTIMESTAMP WITH LOCAL TIME ZONE);
SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-20092:00:00');
SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP'2009-01-01 2:00:00');
SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP'2009-01-01 2:00:00 -08:00');
SQL> commit;
SQL> select * from table_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时区的时间，然后存入数据库后按照databasetimezone的时间保存，最后
在客户端请求的时候，转换为客户端时区的时间(UTC-7)返回！可以参考以下简单实验：
SQL> ALTER SESSION SET TIME_ZONE='-05:00';
SQL> select * from table_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 TIMEZONE数据类型的返回信息是会相
应改变的。

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

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

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

下面的内容仅供参考(来源于互联网)：

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 LOCALTIME 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字段使用了时区的信息：WITHLOCAL TIME ZONE，将此信息去掉就可以再修改了，修改好了之后需要重启数据库才能生效)

Session 的时区是根据客户端的时区来决定的，当然连接以后也可以通过 alter session 来改变。WITHLOCAL 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 返回 TIMESTAMPWITH 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-ddhh24: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 dubuggingpurpose.

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-mmhh24: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>sqlplussky/xxxx

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

Copyright (c) 1982, 2002, Oracle Corporation. All rightsreserved.

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-ddhh24: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 的时区