Oracle的时区问题 - 永春 - 博客园

标签: | 发表时间:2020-02-05 19:50 | 作者:
出处:https://www.cnblogs.com

总结

  • DATETIMESTAMP类型不支持时区转换。
  • 如果应用和Oracle的时区不一致,那么应该使用 TIMESTAMP WITH LOCAL TIME ZONE
    • 对于JDBC程序来说,JVM时区和用户时区保持一致就行了。
  • 如果应用和Oracle的时区不一致,而且需要保存时区信息,那么应该使用 TIMESTAMP WITH TIME ZONE
  • 格式化日期时间字符串函数 TO_CHAR
    • 对于 TIMESTAMP WITH TIME ZONE来说,使用 TO_CHAR时要注意让它输出时区信息( TZH:TZM TZR TZD),否则结果会是截断的。
    • 对于 TIMESTAMP WITH LOCAL TIME ZONE来说,使用 TO_CHAR返回的结果会转换时区。
  • 当前日期时间的函数:
    • 除非必要,不要使用 SYSDATESYSTIMESTAMP,这个返回的是数据库所在操作系统的时间。
    • 尽量使用 CURRENT_TIMESTAMP,它返回的是 TIMESTAMP WITH TIME ZONE,能够用来安全的比较时间。
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 和数据库时区:



[email protected]> select dbtimezone from dual;


DBTIME
------
+08:00


[email protected]> 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 类新,有时区信息:



[email protected]> select sysdate from dual;


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

[email protected]> select systimestamp from dual;


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

[email protected]> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

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

[email protected]> 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.

[email protected]> select sysdate from dual;


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


[email protected]> 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 的时区

相关 [oracle 时区 问题] 推荐:

Oracle的时区问题 - 永春 - 博客园

- -
TIMESTAMP类型不支持时区转换. 如果应用和Oracle的时区不一致,那么应该使用. 对于JDBC程序来说,JVM时区和用户时区保持一致就行了. 如果应用和Oracle的时区不一致,而且需要保存时区信息,那么应该使用. 格式化日期时间字符串函数. TIMESTAMP WITH TIME ZONE来说,使用.

MySQL与Oracle的大小写问题

- - 数据库 - ITeye博客
前段时间,维护的一个应用在Oracle测试时出错,该应用原来已经在MySQL上完成测试. 通过查找定位,原因是字段名大小写问题. 下面与大家分离一下MySQL与Oracle在大小写处理上的区别:. 1、在Windows下,数据库名、表名、字段名不区分大小写. 2、大Linux/Unix下,数据库名、表名区分大小写,字段名不区分大小写.

Oracle数据库10个小问题

- - 数据库 - ITeye博客
Oracle 数据库 10 个小问题. Oracle的安装是出名的复杂,特别是在非windows系统中,在网上可以找到遍地的Oracle安装文档,但依然有很多人花了大量时间才安装成功,如果要搭建rac系统,那就更复杂了. 我们也看到Oracle公司也一直在降低安装的复杂度,11g的安装已经比8i,9i方便了一些.

Google、Oracle CEO就Android专利问题共同出庭

- ZeeJee - cnBeta.COM
北京时间9月19日晚间消息,Google CEO拉里・佩奇(Larry Page)和Oracle CEO拉里・埃里森(Larry Ellison)将于当地时间周一同时出现在美国加利福尼亚州圣何塞地方法院,以解决长期威胁Google Android操作系统生存的专利问题. 目前,Android操作系统已在1.5亿台移动设备中使用.

[转]Oracle修改IP地址引起的问题

- - 小鸥的博客
安装Oracle的时候,Oracle默认会在F:\oracle\product\10.2.0\db_1下面创建一个名为“当前IP_orcl”的目录,该目录存储的是oracle实例名为orcl的数据库的配置等信息. 同时Oracle默认在F:\oracle\product\10.2.0\db_1\oc4j\j2ee目录下创建一个名为“OC4J_DBConsole_当前IP_orcl”的目录,该目录存放的是OracleDBConsole服务的相关配置等信息.

实践Oracle与DB2区别及问题解决

- - ITeye博客
项目进入开发阶段的时候,为了方便,一直使用Oracle数据库进行开发, 所以很多sql语句都是在oracle能正常创建的,后期由于项目中嵌入了IBM的产品及其他因素,所以不得不使用db2数据库,切换数据库过程中的区别还是有点大,如:创建表、视图、存储过程、Ibatis支持等等、、、,下面就总结一下我从Oracle数据库切换到DB2数据库碰到的一些问题及如何解决.

win8 64位使用plsql developer连接oracle数据库的问题

- - CSDN博客推荐文章
问题:win8的64位系统上使用plsql developer连接本地oracle数据库时会发生莫名其妙的问题,错误提示框甚至可能为空. 原因:据网上所说,原因估计是在64位系统上安装64位的Oracle数据库,但是没有对应的64位PL/SQL Developer供我们使用,所以此时不能直接连接,需要安装oracle的客户端.

<让oracle跑得更快-1> 引起数据库性能问题的因素

- - ITeye博客
此《让oracle跑得更快》系列参考《让Oracle跑得更快 Oracle.10g性能分析与优化思路》.(谭怀远). 一个 数据库是否存在性能问题,基本上在 系统设计的时候就决定了,这里说的系统设计包含软件的设计,数据库的设计和硬件的设计. 软件的设计包含了软件系统架构的设计,软件代码的编写;数据库的设计包含了数据库的类型选择和根据数据库类型的所有数据库对象的设计;硬件的设计包括存储结构的设计,硬件的性能选择和冗余设计.

Oracle 收购 Ksplice

- feng823 - LinuxTOY
实现无需重启即可为 Linux 内核打安全补丁的 Ksplice 被 Oracle 收购. 在被收购前, Ksplice 为 Fedora, Ubuntu 免费提供该功能,对于 RHEL 和 CentOS 则需要订阅其产品. Oracle 表示将把 Ksplice 带来的零宕机安全更新功能添加到 Oracle 产品订阅服务中,同时停止对其他企业级 Linux 发行版的支持,将 Oracle Unbreakable Linux 打造成唯一具备零宕机安全更新功能的企业级 Linux 发行版.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.