关于Oracle的时区问题

来源：互联网发布：mac os x 10.11破解版编辑：程序博客网时间：2024/05/01 11:17

时区的计算方法：

已知北京时间2013-12-13 13:00，求东京时间

东京时间 = 13:00-(8-9) = 2013-12-13 14:00 注：北京为东八区，东京为东九区

已知纽约时间2013年-12-13 6:00，求旧金山时间

旧金山时间 = 6:00-(-5-(-8)) = 2013-12-13 3:00 注：纽约为西五区、旧金山为西八区

已知北京时间为2013-12-13 12:00，求纽约时间

纽约时间 = 12:00 - (8-(-5)) = -1:00 + 24:00 -1 =2013-12-12 23:00 注：如果计算出来的时间为负数，那么时间部分加24小时，日期部分减去1天

已知纽约时间为2013-12-13 12:00，求北京时间

北京时间 = 12:00 - (-5-8) =25:00 - 24:00 + 1 = 2013-12-14 1:00 注：如果计算出来的时间大于或等于24:00,，那么时间部分减去24:00，日期部分加上1天

今天了解了关于Oracle的时区问题，感觉有很多地方不明白，记录一些要点：

关于时区：

Oracle 9i引入了一种新的特征，可以表示不同的时区。时区是当地时间与英格兰格林威治时间的时差。格林威治时间一度被称为格林威治标准时间（Green Mean Time - GMT），现在被称为协调世界时间（UTC）。时区可以使用与UTC的时差来表示，也可以使用地理区域来表示，在指定时差是，可以使用HH:MI格式，前面有一个加号或减号前缀： + | - HH:MI。

数据库时区与会话时区及一些与时区相关的函数：

数据库时区是由数据库参数TIME_ZONE控制的，DBA可以在数据库的init.ora或spfile.ora文件中修改TIME_ZONE参数的设置，或者使用alter database set time_zone=offset | region来修改TIME_ZONE参数设置。

查看数据库时区：

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
13-DEC-13 09.44.29.332529 AM +08:00

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

从以上的查询结果可以看出，操作系统的时区和数据库时区并不一致，这和我最初的设想不一致。为何会造成这种结果呢？大概是由于在数据库的安装过程中，我并未设置TIME_ZONE参数导致数据库使用了默认设置的缘故。

会话时区是针对特定会话的时区，在默认情况下，会话时区与操作系统时区相同。同样，可以使用alter session set time_zone= offset | region | local | DBTIMEZONE来设置时区。

查看会话时区：

SQL> select sessiontimezone from dual;

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

查看会话时区中的当前日期：

SQL> select current_date from dual;

CURRENT_D
---------
13-DEC-13

获取时区的时差：TZ_OFFSET函数用于获取时区的时差，其参数为时区名。

SQL> select tz_offset('PRC') from dual;

TZ_OFFS
-------
+08:00

此查询说明PRC比GMT早了8个小时。

获取时区名：如果要使用TZ_OFFSET函数获取时差，就必须知道时区名。可以通过查询v$timezone_names视图来获取时区名。

SQL> SELECT * FROM V$TIMEZONE_NAMES WHERE TZNAME='PRC';

TZNAME TZABBREV
------------------------------------------------- ----------------------------------------------------------------
PRC LMT
PRC CST
PRC CDT

查询中国的时区名

时区转换：new_time(x,time_zone1,time_zone2)

SQL> select to_char(new_time(to_date('13-12-2013 17:03','dd-mm-yyyy hh24:mi'),'PST','EST'),'YYYY-MM-DD HH24:MI') from dual;

TO_CHAR(NEW_TIME
----------------
2013-12-13 20:03

时间戳：

oracle 9i数据库引入了一种新的特征，可以存储时间戳，时间戳可以存储世纪、4位年、月、日、时（以24小时格式）、分、秒。与date类型相比，时间戳具有如下优点：

时间戳可以存储秒的小数位；
时间戳可以存储时区；

时间戳的类型：

(1) timestamp：向数据库提供一个timestamp字面值，可以在一个时间值前面指定timestamp关键字，如：TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.SSSSSSSSS'。为秒的小数部分指定9个数字，TIMESTAMP(n)，n指定了小数部分可存储的位数。例如：

SQL> create table t_timestamp(time timestamp(4));

Table created.
SQL> insert into t_timestamp values (timestamp '2013-12-13 23:29:11.8888');

1 row created.

SQL> select * from t_timestamp;

TIME
---------------------------------------------------------------------------
13-DEC-13 11.29.11.8888 PM

(2) timestamp with time zone：timestamp with time zone类型对timestamp进行了扩展，用于存储时区。例如：

SQL> create table t_with_timezone(time timestamp(4) with time zone);

Table created.

SQL> insert into t_with_timezone values(timestamp '2013-12-13 23:46:15.8888');

1 row created.

SQL> select * from t_with_timezone;

TIME
---------------------------------------------------------------------------
13-DEC-13 11.46.15.8888 PM +08:00
(3) timestamp with local time zone：timestamp with local time zone类型是对timestamp进行了扩展，用于存储一个转换为数据库设置的本地时间戳。例如：

SQL> create table t_with_local_timezone (time timestamp(4) with local time zone);

Table created.

SQL> insert into t_with_local_timezone values (timestamp '2013-12-13 11:11:11');

1 row created.

SQL> select * from t_with_local_timezone;

TIME
---------------------------------------------------------------------------
13-DEC-13 11.11.11.0000 AM

SQL> alter session set time_zone='GMT';

Session altered.
SQL> select * from t_with_local_timezone;

TIME
---------------------------------------------------------------------------
13-DEC-13 03.11.11.0000 AM 注意：这个时间才是我的测试数据库里实际存储的时间

0 0