ORACLE 时间与时区（Time and Time Zone）

2025-04-16 技术教程

转：https://www.2cto.com/database/201110/107435.html

一）

Oracle中的四种时间类型

Date

Timestamp

Timestamp with local time zone

Timestamp with time zone

这四种类型中，前两个与时区完全无关，它们的“行为”就像varchar2或者number类型一样，就是你插入时是什么值，那么存储的也是一样的值，同时查询出来的也是一样的值（包括你在.NET环境下用ADO.NET或者ODP.NET进行查询），不存在任何所谓的“参数”设置可以改变它们（当然，你可以改变它们的显示格式，但不是值）。

后两个类型则跟时区信息紧密相关，但它们之间又有较大的不同。timestamp with local time zone事实上并不存储时区信息，在向此类型的列中插入值时，用户提供时区信息，但是Oracle会自动将其转换成dbtimezone下的时间进行存储，所以，timestamp with local time zone有时区信息，即数据库时区，但它并不存储时区信息。当查询发生时，Oracle再将时间转化到客户端的时区（sessiontimezone）进行显示。

不同于timestamp with local time zone这种把时区的转换委托给Oracle服务器，timestamp with time zone则是单纯地把你在insert时提供的时间+时区信息保存到数据库中。

二）

两个与时区相关的参数

Dbtimezone

Sessiontimezone

--会话时区
select sessiontimezone from dual;
---数据库时区
select systimestamp from dual;
--time with zone
select dbtimezone from dual;

前者表示数据库时区，后者表示客户端时区。只有timestamp with local time zone才会受时区修改的影响。这两个参数都可以通过alter命令进行修改。

对客户端操作系统时区的修改将会影响此机器上的oracle客户端的sessiontimezone；修改服务器操作系统时区并不会改变dbtimezone。

SQL>selectdbtimezonefromdual;
DBTIMEZONE
------------------------------
-06:00

SQL>selectsessiontimezonefromdual;
SESSIONTIMEZONE
------------------------------
+08:00

修改客户端或数据库时区的alter命令：

altersessionsettime_zone='+10:00';

alterdatabasesettime_zone='+10:00';

如果数据库中存在有timestamp with local time zone类型的字段，则在修改dbtimezone时会出现ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

三）

如何向数据库插入一个timestamp with time zone或者timestamp with local time zone类型

SQL>createtabletz1(twtztimestampwithtimezone,twltztimestampwithlocaltimezone);

SQL>insertintotz1values(timestamp'2011-01-0315:00:00.000000+05:00',timestamp'2011-01-0315:00:00.000000+05:00');

SQL>selectsessiontimezonefromdual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL>select*fromtz1;
TWTZTWLTZ
---------------------------------------------------------------------------
03-JAN-1103.00.00.000000PM+05:0003-JAN-1106.00.00.000000PM

SQL>altersessionsettime_zone='-06:00';

SQL>select*fromtz1;
TWTZTWLTZ
---------------------------------------------------------------------------
03-JAN-1103.00.00.000000PM+05:0003-JAN-1104.00.00.000000AM

除了采用类似“+05:00”这种形式的时区标识外，还可以使用时区的缩写，比如GMT,PST等等（可以查看系统视图V$TIMEZONE_NAMES）。如果在insert时未给出时区信息，那么默认为当前客户端的时区。

四）

一些与时区相关的函数：

函数

返回值

返回值类型

SYSTIMESTAMP

Current date/time, in Database TZ

TIMESTAMP WITH TIME ZONE

CURRENT_TIMESTAMP

Current date/time, in Client Session TZ

TIMESTAMP WITH TIME ZONE

LOCALTIMESTAMP

Local date/time in Client Session, but with no TZ info

TIMESTAMP

DBTIMEZONE

Database time zone, in HH:MI offset from GMT

VARCHAR2

SESSIONTIMEZONE

Session time zone, in HH:MI offset from GMT

VARCHAR2

EXTRACT (partFROMdate_time)

Extracts year, hour, seconds, time zone name, etc. from a supplied datetime or interval expression.

VARCHAR2

SYS_EXTRACT_UTC(date_time with TZ)

GMT (UTC) time of date/time supplied

TIMESTAMP

TZ_OFFSET(TZ)

Returns hour/minute offset from GMT ofTZ

VARCHAR2

FROM_TZ(timestamp,TZ)

Converts a TIMESTAMP to TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

TO_TIMESTAMP

Convert char + fmt model to TIMESTAMP

TIMESTAMP

TO_TIMESTAMP_TZ

Convert char + fmt model to TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

五）

时区的基本换算

时区分东西，东时区都带+号标识，西时区用-号。在一个特定的时间点，时区号越大（考虑正负号）的地方，时间越晚，比如北京在+08:00区, St. Louis在-06:00区，北京已经是傍晚了，St. Louis还在凌晨，它们之前相差即+08:00-(-06:00)=14（受夏时制的影响，可能会有一个小时的误差）。

六）

应该选择哪种时间类型？

如果需要记录的时间精度超过秒，选择timestamp类型。

如果需要将时间在数据库时区与客户端时区之间进行自动转换，选择timestamp with local time zone。

如果需要记录客户插入的时区信息，选择timestamp with time zone。

七）

ODP.NET与OracleGlobalization

对于timestamp with time zone类型，由于包含了原始时区信息，所以应用程序中对其进行手动的转换也不困难。除了手动方法，我们还可以通过设置OracleGlobalization下的相关属性让ODP.NET为你进行自动转换。看一个例子：

conn.Open();//connectionshouldbeopenedbeforeSetSessionInfo()couldbeinvoked.
//
OracleGlobalizationog=OracleGlobalization.GetClientInfo();
og.TimeZone="America/Chicago";
OracleGlobalization.SetThreadInfo(og);
conn.SetSessionInfo(og);
//
OracleCommandcmd=newOracleCommand();
cmd.Connection=conn;
cmd.CommandText="selecttwtzfromtz1";
OracleDataReaderdr=cmd.ExecuteReader();
if(dr.HasRows)
{
while(dr.Read())
{
OracleTimeStampTZotstz=dr.GetOracleTimeStampTZ(dr.GetOrdinal("twtz"));
Console.WriteLine("twtz:"+otstz.ToString());
}
}
dr.Close();
//
//analiasisnecessarywhenusing'ATLOCAL'predicate
cmd.CommandText="selecttwtzATLOCALastwtzfromtz1";
dr=cmd.ExecuteReader();
if(dr.HasRows)
{
while(dr.Read())
{
OracleTimeStampTZotstz=dr.GetOracleTimeStampTZ(dr.GetOrdinal("twtz"));
Console.WriteLine("twtzATLOCAL:"+otstz.ToString());
}
}
dr.Close();

//output:
twtz:03-JAN-1103.00.00.000000PM+05:00