如何在 Oracle 数据库中处理夏令时

How to handle Day Light Saving in Oracle database

提问by sandeep pandit

In one of the Oracle database server it is showing "+01:00" when I fire the "Select dbtimezone from dual" does that mean in summer the clock will shift one hour ahead ?. In another server it is showing "+00:00" does that mean the database server setting is GMT ? but I am using the sysdate in oracle pl/sql. Client is saying the Aix server is on DST so would that mean the DB server will adopt the AIX server setting after clock change ? How to fix this problem.

在其中一台 Oracle 数据库服务器中，当我触发“Select dbtimezone from dual”时，它显示“+01:00”，这是否意味着在夏季时钟会提前一小时？。在另一台服务器上显示“+00:00”是否意味着数据库服务器设置为 GMT ？但我在 oracle pl/sql 中使用 sysdate。客户端说 Aix 服务器在 DST 上，这是否意味着数据库服务器将在时钟更改后采用 AIX 服务器设置？如何解决这个问题。

采纳答案by sandeep pandit

I resolved this issue by using the below command "Select Systimestamp at time zone 'GMT' from DUAL' This command wil always give the GMT date and time irrespective of OS time.

我通过使用以下命令“从 DUAL 中选择时区‘GMT’的 Systimestamp”解决了此问题，此命令将始终提供 GMT 日期和时间，而与操作系统时间无关。

回答by Wernfried Domscheit

Answer is: It depends.

答案是：视情况而定。

In total your database has threetime zones

您的数据库总共有三个时区

1. Your seesion time zone: SESSIONTIMEZONE

   This you can change by ALTER SESSION SET TIME_ZONE=...at any time. It is relevant for result of

   • CURRENT_DATE
   • LOCALTIMESTAMP
   • CURRENT_TIMESTAMP

   
   

   It is also the target time zone when you do CAST({TIMESTAMP/DATE without any timezone} AS TIMESTAMP WITH {LOCAL} TIME ZONE)

   Default SESSIONTIMEZONEcan be set by environment variable ORA_SDTZor (on Windows) by registry entry HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ(for 32 bit Client), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ(for 64 bit Client).

2. The database time zone: DBTIMEZONE

   Actually this is not so important in daily use, it is relevant only for TIMESTAMP WITH LOCAL TIME ZONEdata type columns and defines the storage format.

   This is NOTthe timezone of SYSDATEor SYSTIMESTAMP!!!

   You cannot change DBTIMEZONEon your database if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONEcolumn and the column contains data. Otherwise it can be changed with ALTER DATABASE SET TIME_ZONE='...';. The change does not take effect until the database has been shut down and restarted.

   DBTIMEZONEis set when database is created. If no time zone is provided while database creation then Oracle defaults to the time zone of the server's operating system.

3. The time zone of database server's operating system:

   This time zone is relevant for result of

   • SYSDATE
   • SYSTIMESTAMP

   
   

   Naturally this time zone cannot be changed on database level. In case your home country uses Daylight Saving Times, this time zone may change twice a year. You can interrogate it with SELECT TO_CHAR(SYSTIMESTAMP, 'tzr') FROM dual;, for instance.

1. 您看到的时区： SESSIONTIMEZONE

   您可以随时更改此设置ALTER SESSION SET TIME_ZONE=...。它与结果相关

   • CURRENT_DATE
   • LOCALTIMESTAMP
   • CURRENT_TIMESTAMP

   
   

   这也是您执行时的目标时区 CAST({TIMESTAMP/DATE without any timezone} AS TIMESTAMP WITH {LOCAL} TIME ZONE)

   默认值SESSIONTIMEZONE可以通过环境变量ORA_SDTZ或（在 Windows 上）通过注册表项HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ（对于 32 位客户端）分别设置。HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ（对于 64 位客户端）。

2. 数据库时区： DBTIMEZONE

   其实这在日常使用中并不是那么重要，它只与TIMESTAMP WITH LOCAL TIME ZONE数据类型列相关， 并定义了存储格式。

   这不是SYSDATE或的时区SYSTIMESTAMP！

   DBTIMEZONE如果数据库包含带有TIMESTAMP WITH LOCAL TIME ZONE列的表并且该列包含数据，则您无法更改数据库。否则它可以用 更改ALTER DATABASE SET TIME_ZONE='...';。直到数据库关闭并重新启动后，更改才会生效。

   DBTIMEZONE在创建数据库时设置。如果在创建数据库时未提供时区，则 Oracle 默认使用服务器操作系统的时区。

3. 数据库服务器操作系统的时区：

   此时区与结果相关

   • SYSDATE
   • SYSTIMESTAMP

   
   

   当然，这个时区不能在数据库级别更改。如果您所在的国家/地区使用夏令时，则该时区可能每年更改两次。例如，您可以使用 询问它SELECT TO_CHAR(SYSTIMESTAMP, 'tzr') FROM dual;。

So, if your DB Server OS is setup properly, then you should get summer times from next week on (at least for Europe)

因此，如果您的数据库服务器操作系统设置正确，那么您应该从下周开始（至少在欧洲）获得夏季时间

回答by Lalit Kumar B

In one of the Oracle database server it is showing "+01:00" when I fire the "Select dbtimezone from dual" does that mean in summer the clock will shift one hour ahead ?

在其中一台 Oracle 数据库服务器中，当我触发“Select dbtimezone from dual”时，它显示“+01:00”，这是否意味着在夏季时钟会提前一小时？

It means your database timezone is +01:00compared to UTC time zone.

这意味着将您的数据库时区+01:00与UTC 时区进行比较。

From docs,

从文档，

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

DBTIMEZONE 返回数据库时区的值。返回类型是时区偏移量（格式为 '[+|-]TZH:TZM' 的字符类型）或时区区域名称，具体取决于用户在最近的 CREATE DATABASE 中指定数据库时区值的方式或 ALTER DATABASE 语句。

So, database's DBTIMEZONEinherits the timezone value from DB Server OS.

因此，数据库DBTIMEZONE从DB Server OS继承时区值。

While SESSIONTIMEZONEcould be overridden at session level by an alter session statement.

虽然SESSIONTIMEZONE可以在会话级别被更改会话语句覆盖。

For example,

例如，

ALTER SESSION SET TIME_ZONE=<timezone>;

This modifies the time zone of the TIMESTAMP WITH LOCAL TIME ZONE.

这会修改TIMESTAMP WITH LOCAL TIME ZONE.

---

---

In another server it is showing "+00:00" does that mean the database server setting is GMT ?

在另一台服务器上显示“+00:00”是否意味着数据库服务器设置为 GMT ？

+00:00can be assumed that the database time zone is set to UTC time zone.

+00:00可以假设数据库时区设置为UTC 时区。

For example,

例如，

SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

Read more from documentation.

从文档中阅读更多内容。

回答by kk1992

Oracle Database automatically determines whether Daylight Saving Time is in effect for a specified time zone and returns the corresponding local time. Normally, date/time values are sufficient to allow Oracle Database to determine whether Daylight Saving Time is in effect for a specified time zone. The periods when Daylight Saving Time begins or ends are boundary cases. For example, in the Eastern region of the United States, the time changes from 01:59:59 a.m. to 3:00:00 a.m. when Daylight Saving Time goes into effect. The interval between 02:00:00 and 02:59:59 a.m. does not exist. Values in that interval are invalid. When Daylight Saving Time ends, the time changes from 02:00:00 a.m. to 01:00:01 a.m. Further explaination can be found here. http://docs.oracle.com/cd/E18283_01/server.112/e10729/ch4datetime.htm#insertedID11

Oracle 数据库自动确定夏令时是否对指定时区生效并返回相应的本地时间。通常，日期/时间值足以让 Oracle 数据库确定夏令时是否对指定时区有效。夏令时开始或结束的时间段是边界情况。例如，在美国东部地区，夏令时生效时，时间从上午 01:59:59 更改为凌晨 3:00:00。02:00:00 和 02:59:59 am 之间的时间间隔不存在。该间隔中的值无效。当夏令时结束时，时间从 02:00:00 am 变为 01:00:01 am 进一步的解释可以在这里找到。 http://docs.oracle.com/cd/E18283_01/server.112/e10729/ch4datetime.htm#insertedID11