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

Question

Oracle 中是否有一种流畅的方法可以在当前时刻(当我执行调用时(获取 SESSIONTIME zone 和 DBTIME zone 之间的数字差异？

例如：

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 实际上提供了一种计算两个给定时区之间差异的方法？

Answer 1

关于使用会话时区的小警告

您的第一个示例不是防弹的，因为会话时区可以设置为以下任一：

• 操作系统本地时区 ( '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

在函数中解析"欧洲/巴黎"字符串会困难得多......您应该使用 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 的内容的可能性感到满意......

Answer 2

你想要

的是不可能的，因为时区不是固定的，而是随着时间的推移而变化的。因此，您无法计算两个时区之间的差异，而只能计算在某个时间点有效的差异。

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

因此，要

计算偏移量，还必须给出要计算偏移量的特定时间点(即应应用哪个版本的时区定义(。

考虑到这一点，如何计算某个时间点两个时区之间的差异变得很清楚：

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