Oracle 10g 时区混淆

Oracle 10g Time Zone Confusion

提问by Cade Roux

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI')
      ,TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI')
      ,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') 
      ,TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') 
      ,TO_CHAR(LOCALTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') 
      ,DBTIMEZONE
      ,SESSIONTIMEZONE
  FROM DUAL;

Is returning:

正在返回：

2012-01-16 11:42
2012-01-16 11:42    
2012-01-16 11:42 -06:00 
2012-01-16 11:42 -06:00 
2012-01-16 11:42 +00:00 
+00:00  
-06:00

It seems to think the database time zone is GMT, yet the SYSDATE is the same as the CURRENT_DATE.

似乎认为数据库时区是 GMT，但 SYSDATE 与 CURRENT_DATE 相同。

When I remote into that server (Windows), the time zone is apparently CST (however, I am aware that this could be picking up my Terminal Services Client Time Zone Offset, but this machine doesn't have Terminal Services on it, just administrative)

当我远程访问该服务器 (Windows) 时，时区显然是 CST（但是，我知道这可能是获取我的终端服务客户端时区偏移量，但是这台机器上没有终端服务，只是管理)

Running the same thing against a server in Amsterdam (4 minutes later all from the same TOAD client), I'm getting:

在阿姆斯特丹的服务器上运行同样的事情（4 分钟后，全部来自同一个 TOAD 客户端），我得到：

2012-01-16 18:46
2012-01-16 11:46    
2012-01-16 18:46 +01:00 
2012-01-16 11:46 -06:00 
2012-01-16 11:46 +00:00 
+02:00  
-06:00

Note the +2, but at least the SYSDATE and CURRENT_DATE are differing.

注意 +2，但至少 SYSDATE 和 CURRENT_DATE 是不同的。

What is going on here? Where does SYSDATE come from and is there anything else which affects it?

这里发生了什么？SYSDATE 来自哪里，还有什么会影响它吗？

It seems like DBTIMEZONE is not used for any of these things? So what is DBTIMEZONE used for?

似乎 DBTIMEZONE 不用于任何这些事情？那么 DBTIMEZONE 有什么用呢？

回答by Chi

There are actually 3 timezones here, not 2

这里实际上有 3 个时区，而不是 2 个

• the timezone of the session/client
  • Shown in SESSIONTIMEZONE
  • This is the timezone of CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP. The difference between those 3 is the return type, they return a DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE respectively)
• The database timezone
  • Shown in DBTIMEZONE
  • This is the the timezone used for the internal storage of TIMESTAMP WITH LOCAL TIME ZONE values. Note that values are converted to/from session timezone on insert/select so it actually isn't as important as it seems
  • This is NOT the timezone of SYSDATE/SYSTIMESTAMP
• The database OS timezone
  • In unix, it is based on the TZ variable when Oracle is started
  • This is the timezone of SYSDATE and SYSTIMESTAMP

• 会话/客户端的时区
  • 显示在 SESSIONTIMEZONE 中
  • 这是 CURRENT_DATE、LOCALTIMESTAMP 和 CURRENT_TIMESTAMP 的时区。这 3 个之间的区别在于返回类型，它们分别返回 DATE、TIMESTAMP 和 TIMESTAMP WITH TIME ZONE）
• 数据库时区
  • 显示在 DBTIMEZONE 中
  • 这是用于内部存储 TIMESTAMP WITH LOCAL TIME ZONE 值的时区。请注意，值在插入/选择时转换为/从会话时区转换，因此它实际上并不像看起来那么重要
  • 这不是 SYSDATE/SYSTIMESTAMP 的时区
• 数据库操作系统时区
  • 在unix中，是基于Oracle启动时的TZ变量
  • 这是 SYSDATE 和 SYSTIMESTAMP 的时区

In your first example, I can see that the session TZ is UTC-6, the database TZ is UTC, and the database OS timezone is UTC-6.

在您的第一个示例中，我可以看到会话 TZ 是 UTC-6，数据库 TZ 是 UTC，数据库操作系统时区是 UTC-6。

In your second example, I can see that the session TZ is UTC-6, the database TZ is UTC+2, and the database OS timezone is UTC+1.

在您的第二个示例中，我可以看到会话 TZ 是 UTC-6，数据库 TZ 是 UTC+2，数据库操作系统时区是 UTC+1。

回答by YoYo

The details are in the fine print of the documentation. Take a look at the Return Type, and the actual Timezone the DATE or TIMESTAMP is calculated in.

详细信息在文档的细则中。查看返回类型，以及计算 DATE 或 TIMESTAMP 的实际时区。

1. SYSDATE
   • Return Type: DATE
   • Time Zone: Host OS of Database Server
2. CURRENT_DATE
   • Return Type: DATE
   • Time Zone: Session
3. SYSTIMESTAMP
   • Return Type: TIMESTAMP WITH TIME ZONE
   • Time Zone: Host OS of Database Server
4. CURRENT_SYSTIMESTAMP
   • Return Type: TIMESTAMP WITH TIME ZONE
   • Time Zone: Session
5. LOCALTIMESTAMP
   • Return Type: TIMESTAMP
   • Time Zone: Session
6. DBTIMEZONE
   • Time Zone: DB Time Zone. Inherits from DB Server OS, but can be overridden using set at DB Creation or Alter using TIME_ZONE DB Parameter (SET TIME_ZONE=...). This affects the time zone used for TIMESTAMP WITH LOCAL TIME ZONE datataypes.
7. SESSIONTIMEZONE
   • Time Zone: Session Timezone. Inherits from Session hosting OS, but can be overridden using ALTER SESSION (ALTER SESSION SET TIME_ZONE=...).

1. 系统日期
   • 返回类型：日期
   • 时区：数据库服务器的主机操作系统
2. 当前的日期
   • 返回类型：日期
   • 时区：会话
3. 系统时间戳
   • 返回类型：TIMESTAMP WITH TIME ZONE
   • 时区：数据库服务器的主机操作系统
4. CURRENT_SYSTIMESTAMP
   • 返回类型：TIMESTAMP WITH TIME ZONE
   • 时区：会话
5. 本地时间戳
   • 返回类型：时间戳
   • 时区：会话
6. 数据库时区
   • 时区：数据库时区。继承自 DB Server OS，但可以使用 set at DB Creation 或 Alter using TIME_ZONE DB Parameter (SET TIME_ZONE=...) 来覆盖。这会影响用于 TIMESTAMP WITH LOCAL TIME ZONE 数据类型的时区。
7. 会话时区
   • 时区：会话时区。继承自会话托管操作系统，但可以使用 ALTER SESSION (ALTER SESSION SET TIME_ZONE=...) 覆盖。

Return Type, indicates whether or not the Timezone is available within the Datatype. If you try to print TZR if datatype does not carry TimeZone, then it will just show up as +00:00 (doesn't mean it is GMT). Otherwise It will show the TimeZone matching either the Database or Session as indicated.

返回类型，指示时区在数据类型中是否可用。如果您尝试在数据类型不携带 TimeZone 的情况下打印 TZR，那么它只会显示为 +00:00（并不意味着它是 GMT）。否则，它将显示与数据库或会话匹配的时区，如所示。

Time Zone, indicates in which Timezone the time is calculated. For matching TimeZone, the same Date/Time will be shown (HH24:MI).

时区，表示时间是在哪个时区计算的。对于匹配时区，将显示相同的日期/时间 (HH24:MI)。

Note that none of the FUNCTIONS return TIME in the Time Zone set with the DB TIME_ZONE (or as returned by the DBTIMEZONE function). That is, none of the functions also return a datatype of TIMESTAMP WITH LOCAL TIME ZONE. Howver you can convert the output of any of the functions that does return a timezone into a different timezone (including DBTIMEZONE) as follows:

请注意，在使用 DB TIME_ZONE 设置的时区（或由 DBTIMEZONE 函数返回）中，没有任何 FUNCTIONS 返回 TIME。也就是说，没有任何函数也返回 TIMESTAMP WITH LOCAL TIME ZONE 的数据类型。但是，您可以将任何返回时区的函数的输出转换为不同的时区（包括 DBTIMEZONE），如下所示：

SELECT SYSTIMESTAMP AT TIME ZONE DBTIMEZONE FROM DUAL;

More information on my blog.

更多信息请见我的博客。

回答by Emmanuel N

Use UTCtime and offset your timezone from UTC, To get UTC in Oracle use SYS_EXTRACT_UTC

使用UTC时间并将时区与 UTC 偏移，要在 Oracle 中获取 UTC 使用SYS_EXTRACT_UTC

Convert SYSTEMDATE to UTC

将 SYSTEMDATE 转换为 UTC

    select sys_extract_utc(systimestamp) from dual;

As for the difference the definition from Oracle documentation might help to explain:

至于差异，Oracle 文档中的定义可能有助于解释：

• LOCALTIMESTAMPreturns the current date and time in the session time zone in a value of datatype TIMESTAMP
• CURRENT_TIMESTAMPreturns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE
• SYSTIMESTAMPreturns the system date, including fractional seconds and time zone, of the system on which the database resides
• CURRENT_DATEreturns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
• SYSDATEreturns the current date and time set for the operating system on which the database resides.
• DBTIMEZONEreturns the value of the database time zone.

• LOCALTIMESTAMP以数据类型 TIMESTAMP 的值返回会话时区中的当前日期和时间
• CURRENT_TIMESTAMP以数据类型 TIMESTAMP WITH TIME ZONE 的值返回会话时区中的当前日期和时间
• SYSTIMESTAMP返回数据库所在系统的系统日期，包括小数秒和时区
• CURRENT_DATE以数据类型为 DATE 的公历中的值返回会话时区中的当前日期。
• SYSDATE返回为数据库所在的操作系统设置的当前日期和时间。
• DBTIMEZONE返回数据库时区的值。