如何获取DBTIMEZONE和SESSIONTIMEZONE之间的数字差异？

Question

如何获取DBTIMEZONE和SESSIONTIMEZONE之间的数字差异？

oracleoracle11goracle10gtimezonetimezone-offset

8

在Oracle中，是否有一种平稳的方法可以在当前时刻（我进行调用时）获取SESSIONTIMEZONE和DBTIMEZONE之间的数值差异？

例如：

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

返回：

+04:00  +07:00

因此，我需要某种函数，通过调用该函数并提供给定参数，可以获得这两个值之间的差异。

对于上面的示例：

SELECT get_numeric_offset(SESSIONTIMEZONE, DBTIMEZONE) FROM DUAL;

将返回-3（符号至关重要）。

当然，我可以通过使用字符串并解析它们，然后进行一些算术运算或类似操作来自己编写此功能（但我仍不认为这是一个非常流畅的解决方案：

SELECT (
        CAST(SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE AS DATE) - 
        CAST(SYSTIMESTAMP AT TIME ZONE DBTIMEZONE AS DATE)
       )*24 
 FROM DUAL;

也许我漏掉了什么，Oracle实际上提供了一种计算两个给定时区之间差异的方法吗？

- whatsupbros

2个回答

6

你想要的不可能实现，因为时区不是固定的，而是随着时间变化。因此，你不能计算两个时区之间的差异，但只能计算在某一特定时间点有效的差异。

时区随时间变化是很常见的。在TZ Database中，每年都会有许多时区变化，例如改变时区的地方，夏令时开始、结束或差异的更改，或者在“随机”时间插入闰秒。

因此，为了计算偏移量，你必须指定一个特定的时间点，以便计算偏移量（即应用哪个版本的时区定义）。

有了这个理解，就清楚了如何在某一特定时间点计算两个时区之间的差异：

select 
  (TIMESTAMP '2012-06-30 22:00:00 US/Pacific') - (TIMESTAMP '2012-06-30 22:00:00 Europe/Berlin') as diff
from
    dual
;

DIFF
------------------------------
+000000000 09:00:00.000000000

当您拥有这些信息时，您必须决定要达到多高的精度才能获得差异。我特意选择了上面的日期，因为在那个晚上插入了一个闰秒。因此，在某个时间点上，您可能会得到不完全是九小时而是九小时一秒钟（或者是九小时减一秒钟）的差异。

同样重要的是，您要插入确切的位置而不是其他时间戳信息。位置可以更改为不同的时区。因此，您必须指定两个位置和一个时间点以获取正确的时区差异。

如果您想舍入差异，则可以使用EXTRACT(HOUR FROM ...)和EXTRACT(MINUTE FROM ...)访问其组件。如果想在秒级别上进行四舍五入，可以使用以下技巧：

select 
    (to_number(to_char(sys_extract_utc(TIMESTAMP '2012-07-01 01:00:00 US/Pacific'), 'SSSSSFF3')) 
    - to_number(to_char(sys_extract_utc(TIMESTAMP '2012-07-01 01:00:00 Europe/Berlin'), 'SSSSSFF3')))/1000 as diff_s
from 
    dual
;

    DIFF_S
----------
    -54000

- stefan.schwetschke

是的，我记得时区会随着时间而改变。我需要从一个时区到另一个时区的当前偏移量（当前时刻），我不需要非常严格的精度（小时就足够了）。 - whatsupbros

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Miklos Aubert · Accepted Answer

关于使用SESSIONTIMEZONE的小警告

你的第一个示例并不是万无一失的，因为会话时区可以设置为以下任意一种：

操作系统本地时区 ('OS_TZ')
数据库时区 ('DB_TZ')
相对于UTC的绝对偏移量（例如，'-05:00'）
时区区域名称（例如，'Europe/London'）

看看这里发生了什么：

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE  |  DBTIMEZONE
         +04:00  |      +07:00

ALTER SESSION SET TIME_ZONE='Europe/Paris';

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE  |  DBTIMEZONE
   Europe/Paris  |      +07:00

在你的函数中解析“Europe/Paris”字符串会更加困难...你应该使用TZ_OFFSET函数，以确保始终获得相同的±HH:MM格式。

ALTER SESSION SET TIME_ZONE='Europe/Paris';

SELECT DBTIMEZONE, SESSIONTIMEZONE, TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL;

 DBTIMEZONE | SESSIONTIMEZONE | TZ_OFFSET(SESSIONTIMEZONE)
     +07:00 |    Europe/Paris |                     +02:00

关于你的第二个解决方案

我认为我更喜欢你的第二个解决方案。你可以使用CURRENT_DATE代替CAST(SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE AS DATE)来缩短它们，它们是等效的：

SELECT (
        CURRENT_DATE - 
        CAST(SYSTIMESTAMP AT TIME ZONE DBTIMEZONE AS DATE)
       )*24 
 FROM DUAL;

你甚至可以在技术上做到这一点！

SELECT (CURRENT_DATE - SYSDATE) * 24 
 FROM DUAL;

但实际上，SYSDATE并不能保证与DBTIMEZONE处于相同的时区。 SYSDATE始终绑定到基础操作系统的时区，而DBTIMEZONE可以在服务器启动后更改。

虽然我在吹毛求疵，但我还应该提醒您，某些国家/地区使用的偏移量不是整数，例如伊朗标准时间是UTC+03:30，缅甸时间是UTC+06:30... 我不知道在生产环境中是否会遇到这种情况，但希望您能接受您的查询可能返回1.5等内容的可能性...