CODEWITHSUNDEEP
oracle-databasetimezoneextractdst
Yael Levani
Yael Levani

Reputation: 11

Daylight saving error

I would like to calculate time diff between to dates in a different time zone. I am converting this two times to Greenwich time. But when I am converting dates in Europe/Moscow timezone the offset is different although there is no daylight saving.

This is the case I checked:

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

And the results are:

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

but 2018 Time Zones - Sochi is UTC + 3h

Upvotes: 1

Views: 167

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

In October 2014 Russia changed their rule for Daylight-Saving. Your Timezone file at Oracle Database could be an old one which does not cover the recent changes. Verify version with 

SELECT * FROM V$TIMEZONE_FILE;

and consider an upgrade, see Upgrading the Time Zone File and Timestamp with Time Zone Data

However, it seems to be a bug in Oracle. Have a look at my query which is a bit more clear than your example:

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.

That's really strange, because TZH:TZM, resp. EXTRACT(TIMEZONE_HOUR FROM ...) returns different value than default NLS_TIMESTAMP_TZ_FORMAT = ... TZH:TZM. For other time zones (e.g. Europe/Zurich) I get always 02:00 - as expected.

You may open a ticket at Oracle support.

I have two databases, an old and a new one. The old one does not reflect recent changes in Russian Daylight-Saving times, the newer one does. However, the error as above appears on both:

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

Upvotes: 1

Related Questions