首页 文章

夏令时错误

提问于
浏览
1

我想计算不同时区的日期之间的时差 . 我将这两次转换为格林威治时间 . 但是当我在 Europe/Moscow 时区转换日期时,偏移量是不同的,尽管没有夏令时 .

这是我检查的情况:

SELECT TO_DATE('5/20/2018 10:05:00 PM','mm/dd/yyyy hh:mi:ss am'),
       TO_DATE('5/20/2018 10:05:00 PM','mm/dd/yyyy hh:mi:ss am') 
             - extract(TIMEZONE_HOUR from from_tz (TO_TIMESTAMP('5/20/2018 10:05:00 PM','mm/dd/yyyy hh:mi:ss am') ,'GMT' ) at time zone 'Europe/Moscow')/24 deptime_GRINICH
from dual
UNION
SELECT TO_DATE('5/24/2018 11:35:00 PM','mm/dd/yyyy hh:mi:ss am'),
       TO_DATE('5/24/2018 11:35:00 PM','mm/dd/yyyy hh:mi:ss am') 
             - extract(TIMEZONE_HOUR from  from_tz (TO_TIMESTAMP('5/24/2018 11:35:00 PM','mm/dd/yyyy hh:mi:ss am') ,'GMT' ) at time zone 'Europe/Moscow')/24 deptime_GRINICH
from dual

结果是:

Date                         date converting to Greenwich time zone
5/20/2018 10:05:00 PM        5/20/2018 6:05:00 PM   --> hour difference  4 hours
5/24/2018 7:35:00 PM         5/24/2018 3:35:00 PM   --> hour difference  4 hours

但2018年时区 - 索契是UTC 3小时

1 回答

  • 1

    2014年10月,俄罗斯改变了日光节约的规则 . Oracle数据库中的时区文件可能是旧文件,但不包含最近的更改 . 验证版本

    SELECT * FROM V$TIMEZONE_FILE;
    

    并考虑升级,请参阅Upgrading the Time Zone File and Timestamp with Time Zone Data

    但是,它似乎是Oracle中的一个错误 . 看看我的查询比你的例子更清晰:

    SELECT *
    FROM NLS_SESSION_PARAMETERS
    WHERE parameter LIKE 'NLS_TIMESTAMP_TZ_FORMAT';
    
    PARAMETER                  VALUE                  
    -------------------------- --------------------------------------
    NLS_TIMESTAMP_TZ_FORMAT    YYYY-MM-DD HH24:MI:SSfmXFF3 fmTZH:TZM                                      
    
    1 row selected.
    
    
    SELECT 
        EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2018-05-20 22:05:00 Europe/Moscow') AS TZ_HOUR, 
        TO_CHAR(TIMESTAMP '2018-05-20 22:05:00 Europe/Moscow', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS ts2,
        TIMESTAMP '2018-05-20 22:05:00 Europe/Moscow' AS ts3
    FROM dual;
    
       TZ_HOUR TS2                            TS3            
    ---------- ------------------------------ ----------------------------------
             4 2018-05-20 22:05:00 +04:00     20.05.2018 22:05:00.000000000 +03:00        
    
    1 row selected.
    

    这真的很奇怪,因为 TZH:TZM ,resp . EXTRACT(TIMEZONE_HOUR FROM ...) 返回的值与默认值 NLS_TIMESTAMP_TZ_FORMAT = ... TZH:TZM 不同 . 对于其他时区(例如 Europe/Zurich ),我总是 02:00 - 正如预期的那样 .

    您可以在Oracle支持中打开一张票 .

    我有两个数据库,一个是旧的,一个是新的 . 旧的并没有反映俄罗斯夏令时的最近变化,而新的变化则是如此 . 但是,上述错误同时出现在:

    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';
    
    SELECT filename, VERSION,
        TO_CHAR(TIMESTAMP '2018-05-20 22:05:00 Europe/Moscow', 'TZH:TZM TZD') AS ts1,
        TIMESTAMP '2018-05-20 22:05:00 Europe/Moscow' AS ts2,
        TO_CHAR(TIMESTAMP '2018-01-20 22:05:00 Europe/Moscow', 'TZH:TZM TZD') AS ts3,
        TIMESTAMP '2018-01-20 22:05:00 Europe/Moscow' AS ts4
    FROM V$TIMEZONE_FILE;
    
    
    
    Old (Daylight-Saving times still existing due to old timezlrg_14.dat file):
    
    FILENAME          VERSION  TS1          TS2                                    TS3           TS4
    ----------------  -------- -----------  ------------------------------------   -----------   -------------------------------------
    timezlrg_14.dat   14       +04:00 MSD   20.05.2018 22:05:00.000000000 +03:00   +03:00 MSK    20.01.2018 22:05:00.000000000 +03:00
    
    New (no Daylight-Saving time changes):
    
    FILENAME          VERSION  TS1          TS2                                    TS3           TS4
    ----------------  -------- -----------  ------------------------------------   -----------   -------------------------------------
    timezlrg_18.dat   18       +04:00 MSK   20.05.2018 22:05:00.000000000 +03:00   +04:00 MSK    20.01.2018 22:05:00.000000000 +03:00
    

相关问题