对Oracle中时区及时间的认识

 对Oracle中时区及时间的认识

作者：范俊  2013-03-24

           前几天遇到一个服务问题，是关于时间同步的。处理过程中我们对Oracle的时区及时间的理解感觉有点模糊，所以专门又查询资料并做了相关验证，强化了一下对相关概念和知识的理解。

 

摘要

            我们通常都清楚以下这点——同样是当前的时间，但在不同时区所显示的时间是有差别的。如，我们所处的时区是" GTM+08:00北京,重庆,香港特别行政区,乌鲁木齐"，当前时间是15:00，那么时区为" GTM-04:00 大西洋时间"则当前时间为3:00，相差整12个小时。

            那么针对时区的差异，Oracle是如何支持的呢？这次我们就来对Oracle中的时区和时间做个了解。

 

一、Oracle中时区/时间相关的类型和函数

1、Oracle中时间相关的类型

            时间相关的类型主要有四种：DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE。

            DATE：存储日期和时间信息，精确到秒。

            TIMESTAMP：DATE类型的扩展，保留小数级别的秒（默认为小数点后６位），不保存时区和地区信息。

            TIMESTAMP WITH TIME ZONE：TIMESTAMP类型的一种扩展，存储时区信息。

            TIMESTAMP WITH LOCAL TIME ZONE：TIMESTAMP类型的另一种扩展，用于存储一个转换为数据库设置的本地时区的时间戳。与TIMESTAMP WITH TIME ZONE类型的区别在于：数据库不保存时区相关信息，而是把客户端输入的时间转换为基于数据库时区的时间进行存储。

 

2、Oracle中时区/时间相关的函数

DBTIMEZONE：返回数据库时区（Returns the value of the database time zone. The value is a time zone offset or a time zone region name）

SESSIONTIMEZONE： 返回当前会话的时区（Returns the value of the current session's time zone）

SYSDATE：返回数据库所在操作系统的时间，类型为Date（Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.）

CURRENT_DATE： 返回基于当前会话时区的时间，类型为Date（Returns the current date in the session time zone in a value in the Gregorian calendar, of the DATE datatype.）

SYSTIMESTAMP：返回数据库所在操作系统时间，类型为TIMESTAMP WITH TIME ZONE （Returns the system date, including fractional seconds and time zone of the system on which the database resides.）

CURRENT_TIMESTAMP：返回基于当前会话时区的时间，类型为TIMESTAMP WITH TIME ZONE（Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE value）

 

二、对Oracle中时区/时间的理解

            在了解以上类型和函数后，并经过了实际验证（大部分的验证网上都有现成的文章，本次就直接在最后附上相关文章链接），基本对Oracle中的’时区信息’以及’时区信息对时间类型数据的影响’有个了解。以下就是整理得出的一些结论：

1、时间相关的类型中，DATE、TIMESTAMP、TIMESTAMP WITH LOCAL TIME ZONE 这三种不存储时区信息，仅 TIMESTAMP WITH TIME ZONE 存储时区信息；

2、数据库时区（即SELECT DBTIMEZONE FROM DUAL;查询出的结果）只会对 TIMESTAMP WITH LOCAL TIME ZONE 类型的数据有影响；

            补充说明：

            a) 并且当数据库包含该类型时，是不允许更改数据库时区的（修改 时会报ORA-30079错误）；     b) TIMESTAMP WITH LOCAL TIME ZONE 类型是把客户端输入的时间转换为基于数据库时区的时间后存入数据库；当用户请求此类型信息时，Oracle把数据转换为用户会话（session）的时区时间返回给用户。所以说——更改数据库时区对已经存入数据库的数据不会有影响（更改前后，已存储的数据都不会改变）。

3、如果数据库类型是DATE，TIMESTAMP类型，则数据值是按传入时间为准（不管时区）；

            例如：假定当前时间为 GTM+08:00  10:00:00

                        机器A时间为  GTM+08:00  10:00:00   存入数据库，值将是10:00:00   

                        机器B时间为  GTM+00:00  2:00:00    存入数据库，值将是2:00:00

4、SYSDATE和SYSTIMESTAMP的返回信息是数据库所在操作系统的信息，与当前用户会话（session）的时区无关；

5、数据库所在操作系统的时间或时区有更改，需要重启数据库才会对SYSDATE和SYSTIMESTAMP生效；

6、当前用户会话（session）的时区缺省为会话访问者操作系统的时区，但可以通过语句（ALTER SESSION SET TIME_ZONE='+8:00';）修改，且修改后不会改变操作系统的时区设置；

 

结语

            以上结论基本都是经过查询官方资料或经验证得出，但也不排除有因理解有误造成的错误。所以如果大家发现有不正确的地方，请及时给予纠正。我将非常感激！谢谢！

 

 

参考资料：
1) Datetime Datatypes and Time Zone Support
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
2) 对时间类型及时间/时区函数，体检看下这篇文章
 http://space.itpub.net/9765498/viewspace-539881
......

注意：转载请保留文章完整，并注明出处！谢谢！