时间戳

首页 > 解决方案 > Oracle 数据库中 DST 更改的时间似乎是凌晨 1 点而不是凌晨 2 点

sql - Oracle 数据库中 DST 更改的时间似乎是凌晨 1 点而不是凌晨 2 点

问题描述

我正在尝试将 Oracle 中的 UTC 时间转换为 BST 时间 - 但是，oracle 中的数据更改时间似乎是凌晨 1 点而不是凌晨 2 点。或者我在这里错过了什么？我使用以下代码来说明和测试问题：

WITH time1 AS (select cast('2020-03-29 01:00:00 UTC' ASTIMESTAMP WITH TIME ZONE) AS UTC_time FROM dual)
SELECT UTC_time, (UTC_time AT TIME ZONE 'Europe/London') AS bst FROM time1

在 1:00:00 UTC，BST 时间是 2:00:00 - 它应该是 1:00:00

标签： sqloracle

解决方案

Oracle 是对的，您的期望似乎是错误的。您可以在此处查看欧洲/伦敦 DST 更改。

IANA 时区数据库具有以下欧洲/伦敦：

# See EU for rules starting in 1996.
...
# Zone  NAME            GMTOFF  RULES   FORMAT  [UNTIL]
Zone    Europe/London   -0:01:15 -      LMT     1847 Dec  1  0:00s
                         0:00   GB-Eire %s      1968 Oct 27
                         1:00   -       BST     1971 Oct 31  2:00u
                         0:00   GB-Eire %s      1996
                         0:00   EU      GMT/BST

因此，自 1996 年以来，英国一直遵循欧盟规则，即：

# Europe

# The following rules are for the European Union and for its
# predecessor organization, the European Communities.
# For brevity they are called "EU rules" elsewhere in this file.

# Rule  NAME    FROM    TO      TYPE    IN      ON      AT      SAVE    LETTER/S
Rule    EU      1977    1980    -       Apr     Sun>=1   1:00u  1:00    S
Rule    EU      1977    only    -       Sep     lastSun  1:00u  0       -
Rule    EU      1978    only    -       Oct      1       1:00u  0       -
Rule    EU      1979    1995    -       Sep     lastSun  1:00u  0       -
Rule    EU      1981    max     -       Mar     lastSun  1:00u  1:00    S
Rule    EU      1996    max     -       Oct     lastSun  1:00u  0       -

所以......在英国（和欧盟其他国家），夏令时从 3 月的最后一个星期日 01:00 UTC 开始应用，明年是 2020-03-29 01:00:00 UTC。并且它一直在 DST 直到 10 月的最后一个星期日 01:00 UTC，明年是 2020-10-25 01:00:00 UTC。

Oracle 遵循这些规则：

with time1 (utc_time) as (
            select timestamp '2020-03-29 00:00:00.000 UTC' FROM dual
  union all select timestamp '2020-03-29 00:59:59.999 UTC' FROM dual
  union all select timestamp '2020-03-29 01:00:00.000 UTC' FROM dual
  union all select timestamp '2020-03-29 01:59:59.999 UTC' FROM dual
  union all select timestamp '2020-03-29 02:00:00.000 UTC' FROM dual
  --
  union all select timestamp '2020-10-25 00:00:00.000 UTC' FROM dual
  union all select timestamp '2020-10-25 00:59:59.999 UTC' FROM dual
  union all select timestamp '2020-10-25 01:00:00.000 UTC' FROM dual
  union all select timestamp '2020-10-25 01:59:59.999 UTC' FROM dual
  union all select timestamp '2020-10-25 02:00:00.000 UTC' FROM dual
)
select utc_time,
  utc_time at time zone 'Europe/London' as london_time,
  to_char(utc_time at time zone 'Europe/London', 'TZD') as "DST?"
from time1
order by utc_time;

UTC_TIME                          LONDON_TIME                                 DST?  
--------------------------------- ------------------------------------------- ------
2020-03-29 00:00:00.000000000 UTC 2020-03-29 00:00:00.000000000 EUROPE/LONDON GMT   
2020-03-29 00:59:59.999000000 UTC 2020-03-29 00:59:59.999000000 EUROPE/LONDON GMT   
2020-03-29 01:00:00.000000000 UTC 2020-03-29 02:00:00.000000000 EUROPE/LONDON BST   
2020-03-29 01:59:59.999000000 UTC 2020-03-29 02:59:59.999000000 EUROPE/LONDON BST   
2020-03-29 02:00:00.000000000 UTC 2020-03-29 03:00:00.000000000 EUROPE/LONDON BST   
2020-10-25 00:00:00.000000000 UTC 2020-10-25 01:00:00.000000000 EUROPE/LONDON BST   
2020-10-25 00:59:59.999000000 UTC 2020-10-25 01:59:59.999000000 EUROPE/LONDON BST   
2020-10-25 01:00:00.000000000 UTC 2020-10-25 01:00:00.000000000 EUROPE/LONDON GMT   
2020-10-25 01:59:59.999000000 UTC 2020-10-25 01:59:59.999000000 EUROPE/LONDON GMT   
2020-10-25 02:00:00.000000000 UTC 2020-10-25 02:00:00.000000000 EUROPE/LONDON GMT

在欧洲中部，夏令时适用于相同的 UTC 时间，但当然当地时间是不同的：

with time1 (utc_time) as (
  ...
)
select utc_time,
  utc_time at time zone 'Europe/Paris' as paris_time,
  to_char(utc_time at time zone 'Europe/Paris', 'TZD') as "DST?"
from time1
order by utc_time;

UTC_TIME                          PARIS_TIME                                 DST?  
--------------------------------- ------------------------------------------ ------
2020-03-29 00:00:00.000000000 UTC 2020-03-29 01:00:00.000000000 EUROPE/PARIS CET   
2020-03-29 00:59:59.999000000 UTC 2020-03-29 01:59:59.999000000 EUROPE/PARIS CET   
2020-03-29 01:00:00.000000000 UTC 2020-03-29 03:00:00.000000000 EUROPE/PARIS CEST  
2020-03-29 01:59:59.999000000 UTC 2020-03-29 03:59:59.999000000 EUROPE/PARIS CEST  
2020-03-29 02:00:00.000000000 UTC 2020-03-29 04:00:00.000000000 EUROPE/PARIS CEST  
2020-10-25 00:00:00.000000000 UTC 2020-10-25 02:00:00.000000000 EUROPE/PARIS CEST  
2020-10-25 00:59:59.999000000 UTC 2020-10-25 02:59:59.999000000 EUROPE/PARIS CEST  
2020-10-25 01:00:00.000000000 UTC 2020-10-25 02:00:00.000000000 EUROPE/PARIS CET   
2020-10-25 01:59:59.999000000 UTC 2020-10-25 02:59:59.999000000 EUROPE/PARIS CET   
2020-10-25 02:00:00.000000000 UTC 2020-10-25 03:00:00.000000000 EUROPE/PARIS CET

可能这就是为什么您希望在 02:00 之前不会看到时间变化，但如果是这样，您会混淆 UTC 和当地时间，和/或英国和中欧。

与您的问题没有直接关系，但在我的 CTE 中，我已从将字符串转换为 timsetamp 转换为使用时间戳文字。除了打字稍微少一点外，格式也很明确。当您进行转换时，您依赖于会话的 NLS 设置与您提供的字符串格式匹配，因此尽管转换对您有用，但它可能不适用于运行您的代码的其他人。如果您不想（或不能）使用文字，那么使用to_timestamp_tz()显式格式掩码会更安全。

推荐阅读