Oracle 10g时区混乱 [英] Oracle 10g Time Zone Confusion

问题描述

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;

正在返回:

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

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

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

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

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)

在阿姆斯特丹的一台服务器上运行同一件事(4分钟后全部来自同一TOAD客户端)，我得到:

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

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

请注意+2，但至少SYSDATE和CURRENT_DATE不同.

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

这是怎么回事? SYSDATE来自哪里，还有其他影响它的东西吗?

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

似乎DBTIMEZONE没有用于任何这些事情?那么DBTIMEZONE的作用是什么?

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

推荐答案

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

There are actually 3 timezones here, not 2

• 会话/客户端的时区
  • 显示在SESSIONTIMEZONE
  • 这是CURRENT_DATE，LOCALTIMESTAMP和CURRENT_TIMESTAMP的时区.这三个之间的区别是返回类型，它们分别返回DATE，TIMESTAMP和TIMESTAMP WITH TIME ZONE)
  • 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)
    • 显示在DBTIMEZONE中
    • 这是用于内部存储带有本地时区值的TIMESTAMP的时区.请注意，值在插入/选择时转换为会话时区/从会话时区转换，因此它实际上并不像看起来那么重要
    • 这不是SYSDATE/SYSTIMESTAMP的时区
    • 在unix中，它基于启动Oracle时的TZ变量
    • 这是SYSDATE和SYSTIMESTAMP的时区

    在您的第一个示例中，我可以看到会话TZ是UTC-6，数据库TZ是UTC，数据库OS时区是UTC-6.

    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 + 2，数据库OS时区是UTC + 1.

    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.

    这篇关于Oracle 10g时区混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！