朱杰兵blog

Oracle中的四种时间类型

Date

Timestamp

Timestamp with local time zone

Timestamp with time zone

这四种类型中，前两个与时区完全无关，它们的“行为”就像varchar2或者number类型一样，就是你插入时是什么值，那么存储的也是一样的值，同时查询出来的也是一样的值（包括你在.NET环境下用ADO.NET或者ODP.NET进行查询），不存在任何所谓的“参数”设置可以改变它们（当然，你可以改变它们的显示格式，但不是值）。

后两个类型则跟时区信息紧密相关，但它们之间又有较大的不同。timestamp with local time zone事实上并不存储时区信息，在向此类型的列中插入值时，用户提供时区信息，但是Oracle会自动将其转换成dbtimezone下的时间进行存储，所以，timestamp with local time zone有时区信息，即数据库时区，但它并不存储时区信息。当查询发生时，Oracle再将时间转化到客户端的时区（sessiontimezone）进行显示。

不同于timestamp with local time zone这种把时区的转换委托给Oracle服务器，timestamp with time zone则是单纯地把你在insert时提供的时间+时区信息保存到数据库中。

二）

两个与时区相关的参数

Dbtimezone

Sessiontimezone

前者表示数据库时区，后者表示客户端时区。只有timestamp with local time zone才会受时区修改的影响。这两个参数都可以通过alter命令进行修改。

对客户端操作系统时区的修改将会影响此机器上的oracle客户端的sessiontimezone；修改服务器操作系统时区并不会改变dbtimezone。

SQL> select dbtimezone from dual;
DBTIMEZONE
------------------------------
-06:00


SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
------------------------------
+08:00

修改客户端或数据库时区的alter命令：

如果数据库中存在有timestamp with local time zone类型的 字段，则在修改dbtimezone时会出现ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

三）

如何向数据库插入一个timestamp with time zone或者timestamp with local time zone类型

SQL> create table tz1(twtz timestamp with time zone, twltz timestamp with local time zone);

SQL> insert into tz1 values(timestamp'2011-01-03 15:00:00.000000 +05:00',timestamp'2011-01-03 15:00:00.000000 +05:00');

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL> select * from tz1;
TWTZ                                    TWLTZ
---------------------------------------------------------------------------
03-JAN-11 03.00.00.000000 PM +05:00     03-JAN-11 06.00.00.000000 PM

SQL> alter session set time_zone='-06:00';

SQL> select * from tz1;
TWTZ                                    TWLTZ
---------------------------------------------------------------------------
03-JAN-11 03.00.00.000000 PM +05:00     03-JAN-11 04.00.00.000000 AM

除了采用类似“+05:00”这种形式的时区标识外，还可以使用时区的缩写，比如GMT,PST等等（可以查看系统视图 V$TIMEZONE_NAMES）。如果在insert时未给出时区信息，那么默认为当前客户端的时区。

四）

一些与时区相关的函数：

五）

时区的基本换算

时区分东西，东时区都带+号标识，西时区用-号。在一个特定的时间点，时区号越大（考虑正负号）的地方，时间越晚，比如北京在+08:00区, St. Louis在-06:00区，北京已经是傍晚了，St. Louis还在凌晨，它们之前相差即+08:00-(-06:00)=14（受夏时制的影响，可能会有一个小时的误差）。

例如：

create table d (dt date,dt_timestamp timestamp,dt_timestamp_tz timestamp with time zone,dt_timestamp_ltz timestamp with local time zone)

--insert数据1
insert into d values (sysdate,sysdate,sysdate,sysdate)

--insert数据2

insert into d values (
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm')
)

tzh = 时区小时,tzm = 时区分钟

结果

1 2013-07-31 18:31:37 31-7月 -13 06.31.37.000000 下午 31-7月 -13 06.31.37.000000 下午 +08:00 31-7月 -13 06.31.37.000000 下午
2 2013-07-31 01:00:00 31-7月 -13 01.00.00.123400 上午 31-7月 -13 01.00.00.123400 上午 -05:00 31-7月 -13 02.00.00.123400 下午

红色计算公式:sessiontimezone(+8) - (-5:00) = 13小时,所以01:00:00+13=14点，即02.00.00.123400 下午