Oracle的时区问题

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 

        T4

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

       T4

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