转：Oracle TimeZone
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 时区，也就是客户端连接时的时区（经过实验， 数据库的时区在创建数据库时可以通过在 create database 语句中加上 SET TIME_ZONE = ' { { + | - } hh : mi | time_zone_region } ' 来指定，如果，不指定， 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 的设置也可以进行各种时区之间的转换）。 select u.name || '.' || o.name || '.' || c.name TSLTZcolumn (一般查询后的结果为：OE.ORDERS.ORDER_from sys.obj$ o, sys.col$ c, sys.user$ u where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#; 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 SYS@SKYDB> select sessiontimezone from dual; SESSIONTIMEZONE
TZ_OFFSET ( { 'time_zone_name' SELECT TZ_OFFSET('US/Eastern') FROM DUAL; TZ_OFFS 其中 time_zone_name 也可以从 V$TIMEZONE_NAMES 获得。 3. 几个内建时间函数的比较 sysdate/systimestamp 都是返回数据库的时间并且使用数据库的时区， SYS@SKYDB> select sysdate from dual; SYSDATE SYSTIMESTAMP Total System Global Area 89202456 bytes SYSDATE SYS@SKYDB> select systimestamp from dual; 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- 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 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, 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 SettingsAdministrator>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 的时区 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8337095/viewspace-1032115/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- Exclusive access could not be obtained because the database is in use
- select the lock block in MSSQL
- startup and shutdown RAC
- housekeep arch logs (tested on AIX)
- ora-00119, ora-00131
- crsctl start crs for RAC on PRD
- wmi provider error 0x800742a2 :: MSSQL instance can not start
- [转载牛人教程和自己的实验结果] 7.手工备份恢复--复制数据库(练习9)
- ORA-12560
- Stop runnung job
