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

How to get the numeric difference between DBTIMEZONE and SESSIONTIMEZONE?

提问by ZZa

Is there in Oracle a smooth way to get the numeric difference between SESSIONTIMEZONE and DBTIMEZONE at the current moment (when I perform the call)?

Oracle 中是否有一种平滑的方法来获取当前时刻（当我执行调用时）SESSIONTIMEZONE 和 DBTIMEZONE 之间的数字差异？

For instance:

例如：

SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

Returns:

返回：

+04:00  +07:00

So, i need some kind of function, by calling which with given parameters, I get the difference between these two values.

所以，我需要某种函数，通过使用给定的参数调用它，我得到这两个值之间的差异。

For the instance above:

对于上面的例子：

SELECT get_numeric_offset(SESSIONTIMEZONE, DBTIMEZONE) FROM DUAL;

Would return -3 (the sign is crucial).

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

Of course, it's possible for me to write this function myself by working with strings and parsing them and then proceeding some arithmetical operations or do something like this (which I still don't consider as a pretty smooth solution:

当然，我可以通过处理字符串并解析它们然后进行一些算术运算或执行类似的操作来自己编写此函数（我仍然认为这不是一个非常顺利的解决方案：

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

Maybe I missed something and Oracle actually provides a way to calculate difference between two given timezones?

也许我错过了一些东西，而 Oracle 实际上提供了一种计算两个给定时区之间差异的方法？

回答by Miklos Aubert

A little warning on using SESSIONTIMEZONE

关于使用 SESSIONTIMEZONE 的一点警告

Your first example is not bullet-proof, as the session time zone can be set to either of the following :

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

• Operating system local time zone ('OS_TZ')
• Database time zone ('DB_TZ')
• Absolute offset from UTC (for example, '-05:00')
• Time zone region name (for example, 'Europe/London')

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

Just look at what happens here :

看看这里发生了什么：

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

Parsing that 'Europe/Paris' string in your function is going to be a lot harder... You should use the TZ_OFFSETfunction, to ensure that you always get the same ±HH:MMformat.

在您的函数中解析“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

About your second solution

关于你的第二个解决方案

I think I like your second solution better. You can shorten it by using CURRENT_DATEinstead of CAST(SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE AS DATE), they are equivalent :

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

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

You could technically even do this !

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

SELECT (CURRENT_DATE - SYSDATE) * 24 
 FROM DUAL;

BUT actually, SYSDATEis not guaranteed to be on the same timezone as DBTIMEZONE. SYSDATEis always bound to the underlying OS' timezone, while DBTIMEZONEcan be altered once the server has been started.

但实际上，SYSDATE不能保证与DBTIMEZONE. SYSDATE始终绑定到底层操作系统的时区，而DBTIMEZONE一旦服务器启动就可以更改。

And while I'm splitting hairs, I should also remind you that certain countries / regions use offsets that are not whole numbers, for example Iran Standard Time is UTC+03:30, Myanmar Time is UTC+06:30... I don't know if you'll ever encounter this in your production environment, but I hope you're okay with the possibility that your query returns something like 1.5...

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

回答by stefan.schwetschke

What you want is not possible, because timezones are not fixed, but change over time. Hence you cannot calculate THE difference between two timezones, but only the difference that is valid at a certain point in time.

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

It is quite common, that timezones change over time. In the TZ Databasethere are each year many changes for the time zones, e.g. places changing their time zone or changes on the start, end or difference of the day light saving time, or leap seconds getting inserted at "random" times.

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

So to calculate the offset, you must also give a particular point in time, for which you want the offset calculated (i.e. which version of the time zone definitions should be applied).

因此，要计算偏移量，您还必须给出一个特定的时间点，您希望为其计算偏移量（即应应用哪个版本的时区定义）。

With this in mind, it becomes clear, how to calculate the difference between two time zones at a certain point in time:

考虑到这一点，很明显，如何计算某个时间点两个时区之间的差异：

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

When you have this information, you have to decide, up to which precision you want the difference. I have chosen the date above on purpose, because on that night a leap second was inserted. So there might be some point in time, where you will get a difference of not exactly nine hours but of nine hours and one second (or is it nine hours minus one second).

当您获得这些信息时，您必须决定您希望差异达到的精度。我故意选择了上面的日期，因为那天晚上插入了闰秒。因此，可能在某个时间点，您会得到不完全是 9 小时，而是 9 小时 1 秒（或者是 9 小时减去 1 秒）的差异。

It is also important, that you insert the exact locations and not some other timestamp information. Locations can change to different time zones. Hence you have to specify tow locations and a point in time to get the correct time zone difference.

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

If you want to round the difference, you can access its components with EXTRACT(HOUR FROM ...)and EXTRACT(MINUTE FROM ...). If want to round at the second level, you can use the following trick:

如果要舍入差值，可以使用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