ITPub博客

首页 > 数据库 > Oracle > Oracle timezone的相关知识

Oracle timezone的相关知识

原创 Oracle 作者:crystal_ocean 时间:2014-02-12 19:00:29 0 删除 编辑
目前我工作中生产环境都是面向国际化的应用,会遇到有些关于时区的问题,在此做下总结。

oracle中的时区大体分为两类:数据库时区、session 时区。我们可以通过以下方式来查看:

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-08:00

数据库时区可以在建库的时候指定,或者在数据库建成后使用 alter database  set time_zone='+8:00' 进行修改,修改后重启库才能生效。
session时区使用 alter session set time_zone=‘+8:00进行修改。

在这里我们不得不提的是和时区相关的数据类型,因为有这些数据类型,时区的设置才有意义。
与时区相关的数据类型:DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE 等

DATE:存储的是日期+时间,精确到秒级,不保存时区和地区信息。
SQL> select sysdate from dual;

SYSDATE
-------------------
2014-02-12 01:12:18

TIMESTAMP:DATE类型的扩展,保留小数级别的秒,默认为小数点后6位,不保存时区和地区信息。
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-FEB-14 01.14.12.945256 AM

SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF';       ----修改TIMESTAMP数据类型的格式

Session altered.

SQL> select localtimestamp from dual;

LOCALTIMESTAMP

---------------------------------------------------------------------------
2014-02-12 01:28:31.652888


TIMESTAMP WITH TIME ZONE:存储带时区信息的TIMESTAMP,此类型的数据在保存到数据库时带有当前客户端的session timezone,无论在什么时区查看这些数据的时候,数据都不会随时区的不同而变化。

SQL> create table t1 (id number,time timestamp with time zone);         ----创建t1表,其中time 列的数据类型是timestamp with time zone

Table created.

SQL> select sessiontimezone from dual;                                                 -----当前客户端的session timezone 是 -8:00
 
SESSIONTIMEZONE

---------------------------------------------------------------------------
-08:00


SQL> insert into t1 values(1,timestamp '2014-02-12 02:00:00');          ----向t1表中插入一条数据  
1 row created.

SQL> select * from t1;                                              ----查看t1表,其中time列带时区显示,并且时区为数据被插入时的session timezone

        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

SQL> alter session set time_zone='-6:00';                                    ----修改当前客户端的session timezone为 -6:00

Session altered.

SQL> select * from t1;                                                                ----再次查看t1表,其中time列数据无变化。

        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00


TIMESTAMP WITH LOCAL TIME ZONE另一种不同类型的TIMESTAMP,数据库不保存时区相关信息,而是把客户端输入的时间转换为基于
database timezone的时间后存入数据库,(这也就是database tmiezone设置的意义所在,作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺)。
当用户请求此类型信息时,Oracle把数据转换为用户session的时区时间返回给用户。

SQL> create table t2(id number,time timestamp with local time zone);        ----- 创建t2表,其中time列为TIMESTAMP WITH LOCAL TIME ZONE

Table created.

SQL> insert into t2 values(1,timestamp '2014-02-12 02:10:00 -8:00');      

1 row created.                                       -----在t2表插入数据指定时区为-8:00,实际在保存到数据库时转化为基于database timezone的时间保存

SQL> select sessiontimezone from dual;              -----当前客户端的session timezone 为 -6:00

SESSIONTIMEZONE
---------------------------------------------------------------------------
-06:00

SQL> select * from t2;                      -----查看时oracle将数据转换成当前客户端session timezone的时间

        ID      TIME
----------   ---------------------------------------------------------------------------
         1       2014-02-12 04:10:00.000000


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

不需要保存时区/地区信息的时候,选择使用TIMESTAMP数据类型

需要保存时区/地区信息的时候,请选择使用TIMESTAMP WITH TIME ZONE数据类型。比如一个跨国银行业务应用系统,需要精确纪录每一笔交易
的时间和地点(时区),在这种情况下就需要纪录时区相关信息。

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












来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29457434/viewspace-1080444/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-23

  • 博文量
    13
  • 访问量
    89600