关于timestamp with local time zone
Posted: February 1, 2012 | Author: Cui Hua | Filed under: Oracle | Leave a comment »有朋友问我：Oracle数据迁移时源库和目标库的时区不一样，这对于迁移数据而言是否有影响？
这个是没有影响的。
Oracle里跟时区有关的数据类型有两种，分别是timestamp with time zone和timestamp with local time zone，Oracle分别用13个byte和11个byte来存储他们。
这里面timestamp with time zone没什么好说的，因为里面的时区是用2个byte来固定的存储时区的偏移量，所以源库和目标库的时区即使不一样，对原始数据也没有影响。
timestamp with local time zone跟timestamp with time zone最大的区别就是timestamp with local time zone不会具体存储时区的偏移量，而是会根据dbtimezone和用户插入数据时指定的时区（通常就等于sessiontimezone）之间的差值来调整所存储的时间：
我们来看一个实例：
先来看数据库1：
数据库1：
SQL> select dbtimezone from dual;
DBTIMEZONE
———-
+08:00
SQL> select systimestamp from dual;
SYSTIMESTAMP
——————————————————————————–
01-2月 -12 04.15.54.224786 下午 +08:00
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
—————————————————————————
+08:00
现在这个库的systimestamp中包含的时区和dbtimezone相同，所以当我插入systimestamp的时候，Oracle 不会对timestamp with local time zone做时间上的调整：
SQL> create table t2(col1 timestamp with local time zone);
Table created
SQL> insert into t2 values(systimestamp);
1 row inserted
SQL> commit;
Commit complete
SQL> select col1,dump(col1) from t2;
COL1 DUMP(COL1)
————————————————– —————————————————————
01-2月 -12 04.18.58.839122 下午 Typ=231 Len=11: 120,112,2,1,17,19,59,50,3,252,80
从结果里我们看到，17减1后是16，也就是下午4点，所以这里是没有发生时间的调整的，原因我上面已经说过了。
我们修改一下sessiontimezone后再插入一条记录，我把sessiontimezone修改为洛杉矶所在的时区：
SQL> ALTER SESSION SET TIME_ZONE = ‘-08:00′;
Session altered
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
—————————————————————————
-08:00
注意，这个时候不能再插入systimestamp了，systimestamp跟sessiontimezone和dbtimezone没有关系，它只取决于database server端环境变量TZ的设置：
SQL> select systimestamp,current_timestamp from dual;
SYSTIMESTAMP CURRENT_TIMESTAMP
————————————————————– ————————————————-
01-2月 -12 04.37.19.615042 下午 +08:00 01-2月 -12 12.37.19.615109 上午 -08:00
SQL> select * from v$timezone_names where tzname like ‘America/Los%’;
TZNAME TZABBREV
—————————————————————- —————————————————————-
America/Los_Angeles LMT
America/Los_Angeles PST
America/Los_Angeles PDT
America/Los_Angeles PWT
America/Los_Angeles PPT
SQL> insert into t2 values(to_timestamp_tz(’2012-02-01 16:38:19 America/Los_Angeles’,'YYYY-MM-DD HH24:MI:SS TZR’));
1 row inserted
SQL> commit;
Commit complete
SQL> select col1,dump(col1) from t2;
COL1 DUMP(COL1)
————————————————– ——————————————————————————–
01-2月 -12 12.18.58.839122 上午 Typ=231 Len=11: 120,112,2,1,17,19,59,50,3,252,80
01-2月 -12 04.38.19.000000 下午 Typ=231 Len=7: 120,112,2,2,9,39,20
从结果里我们可以看到，之前插入的那条记录显示结果变成了2月1日上午零点18分，这是正常的，因为timestamp with local time zone的显示结果会随着sessiontimezone的改变而改变，洛杉矶滞后于北京16个小时，所以我们在16点18分插入的那条记录的显示结果变成了上午零点18分。
现在我们来解释刚刚插入的第二条记录的显示结果：
插入的时候我们指定了是以洛杉矶当地时间16点38分19秒，而现在的sessiontimezone就是洛杉矶所在的时区，所以第二条记录的显示结果就是16点38分19秒。
现在第二条记录Oracle实际上的存储结果是120,112,2,2,9,39,20，这里因为dbtimezone是+08:00，插入的时候我们指定了是以洛杉矶当地时间（相当于sessiontimezone是-08:00），所以Oracle要对timestamp with local time zone实际存储结果加上sessiontimezone和dbtimezone的差值，也就是16个小时。16点38分19秒加上16是32点38分19秒，也就是第二天早上8点38分19秒，所以这里Oracle实际的存储结果变成了2,2,9,39,20。
我们导出上述表：
oracle:/nbsdata01/oradata/testdb>exp \’sys/oracle@nbsdev as sysdba\’ file=t2.dmp tables=t2
Export: Release 11.2.0.1.0 – Production on Wed Feb 1 17:11:25 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path …
. . exporting table T2 2 rows exported
Export terminated successfully without warnings.
现在我们切换到目标数据库2，这个库里dbtimezone和数据库1的dbtimezone是不一样的，这里我用dbtimezone的差异来模拟时区的差异：
数据库2：
SQL> select dbtimezone from dual;
DBTIMEZONE
——————
+00:00
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
——————————————————————————–
+08:00
我们尝试导入刚才在数据库1里导出的表t2：
oracle:/nbsdata01/oradata/testdb>imp \’sys/oracle@testdb as sysdba\’ file=t2.dmp tables=t2 ignore=true
Import: Release 11.2.0.1.0 – Production on Wed Feb 1 17:16:50 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS’s objects into SYS
. importing SYS’s objects into SYS
. . importing table “T2″ 2 rows imported
Import terminated successfully without warnings.
现在我们来看一下导入的结果：
SQL> select col1,dump(col1) from t2;
COL1
—————————————————————————
DUMP(COL1)
——————————————————————————–
01-FEB-12 04.18.58.839122 PM Typ=231 Len=11: 120,112,2,1,9,19,59,50,3,252,80
02-FEB-12 08.38.19.000000 AM Typ=231 Len=7: 120,112,2,2,1,39,20
现在我们再次回到数据库1并将sessiontimezone设成和数据库2一样：
数据库1：
SQL> ALTER SESSION SET TIME_ZONE = ‘+08:00′;
Session altered
SQL> select col1,dump(col1) from t2;
COL1 DUMP(COL1)
————————————————– ——————————————————————————–
01-2月 -12 04.18.58.839122 下午 Typ=231 Len=11: 120,112,2,1,17,19,59,50,3,252,80
02-2月 -12 08.38.19.000000 上午 Typ=231 Len=7: 120,112,2,2,9,39,20
可以看到两条记录的显示时间是一致的，所以对于timestamp with local time zone而言，数据迁移时不同的时区是没有关系的。
这里的本质原因是Oracle在做数据迁移时，对timestamp with local time zone类型的数据Oracle会根据源库和目标库不同的dbtimezone来对timestamp with local time zone类型的数据做转换。这里数据库1的dbtimezone是+08:00，数据库2的dbtimezone是+00:00，所以Oracle对原来的第一条记录的小时17减了8变成了9,第二条记录的小时9减了8变成1。
Recent Comments