关于timestamp with local time zone

有朋友问我:Oracle数据迁移时源库和目标库的时区不一样,这对于迁移数据而言是否有影响?

这个是没有影响的。

 

Oracle里跟时区有关的数据类型有两种,分别是timestamp with time zonetimestamp with local time zoneOracle分别用13byte11byte来存储他们。

这里面timestamp with time zone没什么好说的,因为里面的时区是用2byte来固定的存储时区的偏移量,所以源库和目标库的时区即使不一样,对原始数据也没有影响。

 

timestamp with local time zonetimestamp 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

从结果里我们看到,171后是16,也就是下午4点,所以这里是没有发生时间的调整的,原因我上面已经说过了

 

我们修改一下sessiontimezone后再插入一条记录,我把sessiontimezone修改为洛杉矶所在的时区:

SQL> ALTER SESSION SET TIME_ZONE = ‘-08:00’;

 

Session altered

 

SQL> select sessiontimezone from dual;

 

SESSIONTIMEZONE

—————————————————————————

-08:00

 

注意,这个时候不能再插入systimestamp了,systimestampsessiontimezonedbtimezone没有关系,它只取决于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

从结果里我们可以看到,之前插入的那条记录显示结果变成了21日上午零点18,这是正常的,因为timestamp with local time zone的显示结果会随着sessiontimezone的改变而改变,洛杉矶滞后于北京16个小时,所以我们在1618分插入的那条记录的显示结果变成了上午零点18分。

 

现在我们来解释刚刚插入的第二条记录的显示结果:

插入的时候我们指定了是以洛杉矶当地时间163819秒,而现在的sessiontimezone就是洛杉矶所在的时区,所以第二条记录的显示结果就是163819秒。

现在第二条记录Oracle实际上的存储结果是120,112,2,2,9,39,20,这里因为dbtimezone+08:00,插入的时候我们指定了是以洛杉矶当地时间(相当于sessiontimezone-08:00),所以Oracle要对timestamp with local time zone实际存储结果加上sessiontimezonedbtimezone的差值,也就是16个小时。163819秒加上16323819秒,也就是第二天早上83819秒,所以这里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和数据库1dbtimezone是不一样的,这里我用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类型的数据做转换。这里数据库1dbtimezone+08:00,数据库2dbtimezone+00:00,所以Oracle对原来的第一条记录的小时17减了8变成了9,第二条记录的小时9减了8变成1

 



Leave a Reply

Your email address will not be published. Required fields are marked *