Oracle 查看和修改数据库时区

1. 如何查看和修改数据库和session时区

Oracle中相关的时区大体可以分为两类：数据库时区和session时区。可以通过以下方式获得：

查看数据库时区信息：

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

查看session时区信息：

SQL> select sessiontimezone from dual;

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

Database的timezone可以在创建数据库的时候指定，如：

CREATE DATABASE db01
...
SET TIME_ZONE='+08:00';

或者在数据库创建之后通过alter database语句修改，但是只有重启数据库后有效：

ALTER DATABASE SET TIME_ZONE='+08:00';

session的timezone可以简单通过alter session语句修改：

ALTER SESSION SET TIME_ZONE='+08:00';




2. 时区相关的数据类型

和时区相关的数据类型主要有：DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE。粗略介绍如下：

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

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select to_date('2009-01-12 13:24:33','YYYY-MM-DD HH24:MI:SS') from dual;

TO_DATE('2009-01-12
-------------------
2009-01-12 13:24:33

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

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07.21.37.984000 PM

TIMESTAMP WITH TIME ZONE：存储带时区信息的TIMESTAMP(以和UTC时间差或者地区信息的形式保存)。形式大致为：

TIMESTAMP '2009-01-12 8:00:00 +8:00'

TIMESTAMP WITH LOCAL TIME ZONE：另一种不同类型的TIMESTAMP，和TIMESTAMP WITH TIME ZONE类型的区别在于：数据库不保存时区相关信息，而是把客户端输入的时间转换为基于database timezone的时间后存入数据库(这也就是database tmiezone设置的意义所在，作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺)。当用户请求此类型信息时，Oracle把数据转换为用户session的时区时间返回给用户。所以Oracle建议把database timezone设置为标准时间UTC，这样可以节省每次转换所需要的开销，提高性能。

下面是针对以上几种类型所做的实验：




2.确定系统当前的时区：
SQL> select property_value from database_properties where property_name='DBTIMEZONE';

PROPERTY_VALUE
00:00

3.修改当前连接的时区，并设置相关的时间戳格式:
SQL> alter session set time_zone='Asia/Chongqing';

Session altered.

SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS TZD:TZR';

或者

SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS TZH';

Session altered.

SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.