timestamp without time zone 设置空

转载

mob64ca1405d568 2025-01-07 15:27:16

文章标签 oracle时区问题 SQL 数据库 Database 文章分类 架构后端开发

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 select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 02-AUG-06 11.35.05.171000 PM 09:00 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 select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 02-AUG-06 11.29.33.609000 PM 09:00 select * from tztest; A ------------------- B --------------------------------------------------------------------------- C --------------------------------------------------------------------------- D --------------------------------------------------------------------------- 2006-08-02 22:25:59

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。