Oracle的时区问题 #

Oracle的时区问题

Oracle 9i 开始多了 3 个关于时间的数据类型：TIMESTAMP [(precision)] TIMESTAMP [(precision)] WITH TIME ZONE TIMESTAMP [(precision)] WITH LOCAL TIME ZONE，其中 TIMESTAMP [(precision)] WITH TIME ZONE 保存了时区信息。

1. Oracle 的时区设置

    Oracle 的时区可以分为两种，一种是数据库的时区，一种是 session 时区，也就是客户端连接时的时区（经过实验，连接以后再修改客户端的时区，session 的时区不会更改）。

    数据库的时区在创建数据库时可以通过在 create database 语句中加上 SET TIME_ZONE = ' { { + | - } hh : mi | time_zone_region } ' 来指定，如果，不指定，默认是按照数据库所在的操作系统时区来设定的。创建之后，可以通过 alter database 来修改。其中 time_zone_region 参数可以通过查询 V$TIMEZONE_NAMES 动态视图来获得所有支持的值。修改之后，需要重启数据库才能生效。经常有人会碰到无法修改的情况：

SQL> alter database set time_zone='+06:00';
alter database set time_zone='+06:00'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

    TOM 对此问题有过解释，TIME_ZONE
的设定主要是为了 WITH LOCAL TIME ZONE，当 session 的时区和数据库的时区不同时，oracle 根据时区的差距转换到数据库的时间，再保存到数据库的 WITH LOCAL TIME ZONE 类型中，他是不保存时区的，所以需要 TIME_ZONE 来进行各种时区之间时间的转换（WITH TIME ZONE 类型保存了原始的时区，所以不需要 TIME_ZONE 的设置也可以进行各种时区之间的转换）。但数据库中一旦有了该类型，就不能通过 alter database 修改时区了，会得到上面的错误，可以通过下面的语句获得所有包含该类型的表，将他们删除之后，再修改。

select u.name || '.' || o.name || '.' || c.name TSLTZcolumn 
  from sys.obj$ o, sys.col$ c, sys.user$ u 
where c.type# = 231
   and o.obj# = c.obj# 
   and u.user# = o.owner#;
(一般查询后的结果为：OE.ORDERS.ORDER_DATE，指的是OE用户下的ORDERS表的ORDER_DATE字段使用了时区的信息：WITH LOCAL TIME ZONE，将此信息去掉就可以再修改了，修改好了之后需要重启数据库才能生效)

    Session 的时区是根据客户端的时区来决定的，当然连接以后也可以通过 alter session 来改变。WITH LOCAL TIME ZONE 类型会根据 TIME_ZONE 的设置，自动把时间转换为 session 所在时区的时间显示出来，而 WITH TIME ZONE 因为保存了时区，不需要根据 TIME_ZONE 的设置来转换。

2. 查看时区

    可以分别使用 SESSIONTIMEZONE / DBTIMEZONE 内建函数查看 session 和数据库时区：

SYS@SKYDB> select dbtimezone from dual;

DBTIME
------
+08:00

SYS@SKYDB> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------
+09:00

    另外可以用 TZ_OFFSET 查询某时区和 UTC 之间的差值。

TZ_OFFSET ( { 'time_zone_name'
                        | '{ + | - } hh : mi'
                        | SESSIONTIMEZONE
                        | DBTMEZONE  }
                      )

SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

TZ_OFFS
-------
-04:00

SELECT TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFSET(DBTI
--------------
+08:00

    其中 time_zone_name 也可以从 V$TIMEZONE_NAMES 获得。

3. 几个内建时间函数的比较

    sysdate/systimestamp 都是返回数据库的时间并且使用数据库的时区，他们返回的是操作系统的时间。sysdate 返回的是 date 类型，没有时区信息，操作系统上是什么时间就返回什么时间；systimestamp 返回 TIMESTAMP WITH TIME ZONE 类新，有时区信息：

SYS@SKYDB> select sysdate from dual;

SYSDATE
-------------------
2006-08-03 10:01:31

SYS@SKYDB> select systimestamp from dual;

SYSTIMESTAMP
-----------------------------------------------
03-AUG-06 10.02.21.093000 AM +08:00

SYS@SKYDB> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

修改操作系统时区为 +02:00

SYS@SKYDB> startup
ORACLE instance started.

Total System Global Area   89202456 bytes
Fixed Size                   454424 bytes
Variable Size              62914560 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SYS@SKYDB> select sysdate from dual;

SYSDATE
-------------------
2006-08-03 04:03:37

SYS@SKYDB> select systimestamp from dual;

SYSTIMESTAMP
----------------------------------------------
03-AUG-06 04.04.15.687000 AM +02:00

    注：这是我单位机子上实验的结果，由于建了多个数据库，不知道为什么不能通过 ipc 来连接本地数据了，登陆时使用 sqlplus "/@skydb as
sysdba"，也就是使用了监听器来连接，但在家里做相同的实验，通过 ipc 连接 sqlplus "/as sysdba"，修改时区后，sysdate 依然显示修改前的时间，而 systimestamp 却正确，不知道是什么原因：

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-02-08 22:21:40

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 10.22.38.578000 PM +08:00

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
修改时区为 +09:00
SQL> startup
ORACLE instance started.

Total System Global Area  131145064
bytes

Fixed Size                   453992
bytes

Variable Size             109051904
bytes

Database Buffers           20971520
bytes

Redo Buffers                 667648

bytes

Database mounted.
Database opened.
SQL> select sysdate from dual;

SYSDATE
---------
02-AUG-06

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-08-02 22:32:59              <- 还是之前的时间

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 11.35.05.171000 PM +09:00          <- 时间正确

    另外，有个初始化参数 fixed_date，可以设置 sysdate 返回指定的时间：

alter system set fixed_date='2005-04-04-11-00-00'

this fixed_date is normally used, in oracle, for dubugging purpose.

once finishing it, you can set it back:

alter system set fixed_date=none

    Eygle 的关于这个参数的相关文章：Why
sysdate is fixed    current_timestamp/current_date 也会返回数据库的时间，但转换为 session 的时区进行显示，可以使用 alter session set time_zone 改变 session 时区。

4. 四个日期时间类型的实验

SQL> select dbtimezone from dual;

DBTIME
------
+06:00

SQL> select sessiontimezone from dual;

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

SQL> ed
Wrote file afiedt.buf

  1  create table tztest(a date,
  2  b timestamp(0),
  3  c timestamp(0) with time zone,
  4* d timestamp(0) with local time zone)
SQL> /

Table created.

SQL> alter session set nls_date_format ='yyyy-dd-mm hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-02-08 22:21:40

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 10.22.38.578000 PM +08:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2006-02-08 22:23:50

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 10.24.04.031000 PM +08:00

SQL> insert into tztest
  2  values(sysdate,systimestamp,systimestamp,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tztest;

A
-------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
D
---------------------------------------------------------------------------
2006-02-08 22:25:59
02-AUG-06 10.25.59 PM
02-AUG-06 10.25.59 PM +08:00
02-AUG-06 10.25.59 PM

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

修改了客户端操作系统的时区

C:\Documents and Settings\Administrator>sqlplus sky/xxxx

SQL*Plus: Release 9.2.0.3.0 - Production on Wed Aug 2 23:28:01 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-08-02 22:28:49        <-数据库没有重启，时间依然是修改前的

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 11.29.33.609000 PM +09:00  <- 这里却已经改变了，有时区信息，自动转换了？

SQL> select * from tztest;

A
-------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
D
---------------------------------------------------------------------------
2006-08-02 22:25:59                       <- 没变
02-AUG-06 10.25.59 PM                  <- 没变
02-AUG-06 10.25.59 PM +08:00      <- 保存时区信息
02-AUG-06 11.25.59 PM                  <-自动转换为 session 的时区

时间： 2024-10-25 06:13:35

Oracle的时区问题 #的相关文章

ORACLE 时间 时区转换问题 急 急 急 怎么获取Europe/Athens时区的SYSDATE

问题描述 怎么获取Europe/Athens时区的SYSDATE 解决方案 The current Europe/Athens time zone offset is:UTC/GMT +3 hou或者这样select from_tz(to_timestamp('20091101','YYYYMMDD'), 'Europe/Athens') from dual;解决方案二:select from_tz(to_timestamp(sysdate), 'Europe/Athens') from dua

oracle中 查询时间管理

1.关于时间显示参数 SQL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss'; 会话已更改. SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 14-3月 -07 05.00.33.599000 下午 +08:00

Oracle 10.2.0.4上ora-01882故障解决一例

  任何软件,特别是企业级系统组件的升级工作,是一个非常复杂的过程.升级路径.数据留存预案.回退步骤.原有业务功能冲击程度,都是需要反复测试论证的问题.所有的运维人员在遇到升级问题的时候,都要抱有谨慎的态度. 笔者最近接手一个升级过的系统,在测试过程中遇到了一些问题.经过查找MOS和网络资源加以解决.记录下来,留待需要的朋友.   1.环境介绍   接手的是一个升级到10.2.0.4的Linux版.   SQL> select * from v$version; BANNER ---------

Redhat linux及其Oracle JRE、IBM JRE的时间、时区及夏令时

对于集群和分布式等大型系统来说,时间及其同步是个很重要的问题.经过对redhat linux及JRE(包括oracle JRE,IBM JRE)的研究,现总结如下. 1,时间的查看 通常的时间可分为local时间和UTC时间,local时间在linux下使用date查看: # date Wed Oct 31 17:38:06 BRST 2012 通常这个时间是指所在时区和包括了夏令时计算的时间值.所有使用linux时间的系统打印出的时间都是这个. UTC时间是指标准格林威治及零时区的时间,不包含

Oracle 数据库中的 时间 时区

本节,我会引入很多概念,慢慢的一个一个的消化....... UTC是协调世界时(Universal Time Coordinated)英文缩写:协调世界时(英:Coordinated Universal Time,法:Temps Universel Coordonné),又称世界统一时间,世界标准时间,国际协调时间.英文(CUT)和法文(TUC)的缩写不同,作为妥协,简称UTC.是由国际无线电咨询委员会规定和推荐,并由国际时间局(BIH)负责保持的以秒为基础的时间标度.UTC相当于本初子午线(即

LINUX时区设置及与数据库之间(ORACLE MYSQL)的关系

LINUX时区    LINUX 操作系统时区由/etc/localtime设置,其可以是一个指向/usr/share/zoneinfo下文件的软连接, 当然也可以拷贝,在/usr/share/zoneinfo目录下每个文件都包含了特定地区的时区信息,很多都分为 洲目录/地区目录  如: UTC:GMT标准时间+0时区 CET:欧洲中部时间 EST:美国东部标准时间 Asia/Shanghai:中国上海+8时区  (亚洲目录/上海地区) 我们可以简单的建立一个连接 cd /etc ln -s  

ORACLE基本数据类型总结

     ORACLE基本数据类型(亦叫内置数据类型 built-in datatypes)可以按类型分为:字符串类型.数字类型.日期类型.LOB类型.LONG RAW& RAW类型.ROWID & UROWID类型. 在讲叙字符串类型前,先要讲一下编码.字符串类 型的数据可依编码方式分成数据库字符集(CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB) 两种.数据库中的字符串数据都通过字符集将字符转换为数字后(二进制),才存储到数据块

Oracle SQL 内置函数的使用方法及结果

SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2.CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 fr

ORACLE常用数值函数、转换函数、字符串函数介绍

oracle|函数|转换|字符串 ORACLE常用数值函数.转换函数.字符串函数介绍. 数值函数: abs(m) m的绝对值 mod(m,n) m被n除后的余数 power(m,n) m的n次方 round(m[,n]) m四舍五入至小数点后n位的值(n缺省为0)trunc(m[,n]) m截断n位小数位的值(n缺省为0) -------------------------------------------------------------------------------- 字符函数: