With the upcoming daylight savings weekend, it’s always nice to (re-)discuss datetime and timezone problems.

In our current project we receive EDI files with the customers estimated time of arrival and estimated time of departure. The EDI file contains strings (not dates). Note: they indicate the localtime for which the customer will arrive and depart.

In the database we store dates as timestamps in UTC format. Now, on our different test servers we get different results for some testcases for when they arrive on saturday and leave on monday. Note: on our client side application the localtime is displayed, so a conversion from UTC to the screen is also necessary.

Consinder the following (simplified) table and row indicating arrival and departure at locatime 8:00 AM

CREATE TABLE EDITABLE
( eta_str VARCHAR2(50)
, etd_str VARCHAR2(50)
);
INSERT INTO EDITABLE(eta_str, etd_str) VALUES ('20180324080000','20180326080000');

During the conversion from EDI strings to timestamp we use the following:

set serveroutput on size 100000
declare
  lc_FormatMask CONSTANT VARCHAR2(50) := 'YYYYMMDDHH24MISS';
  l_eta_ts      TIMESTAMP(6);
  l_etd_ts      TIMESTAMP(6);
  l_eta_gui     DATE;
  l_etd_gui     DATE;
begin
  /* get the EDI strings as TimeStamps */
  select Sys_Extract_UTC(To_Timestamp(to_char(to_date(eta_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
       , Sys_Extract_UTC(To_Timestamp(to_char(to_date(etd_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
  into   l_eta_ts
       , l_etd_ts
  from   EDITABLE;

  /* show what is stored */
  dbms_output.put_line('Eta in: ' || to_char(l_eta_ts, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd in: ' || to_char(l_etd_ts, 'YYYYMMDDHH24MISS'));

  /* conversion used by GUI */
  l_eta_gui := CAST(from_tz(l_eta_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);
  l_etd_gui := CAST(from_tz(l_etd_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);

  /* show what is displayed */
  dbms_output.put_line('Eta gui: ' || to_char(l_eta_gui, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd gui: ' || to_char(l_etd_gui, 'YYYYMMDDHH24MISS'));
end;
/

Now, on one server there was some interesting output shown:
Eta in: 20180324070000
Etd in: 20180326070000
Eta gui: 20180324080000
Etd gui: 20180326090000
The input datetime is not converted to the proper UTC datetime, while the output converts it correctly to locatime. Resulting in the wrong time.

After looking too long to the output of SELECT * FROM SYS.NLS_SESSION_PARAMETERS; noticing nothing strange, the following statement was issued:

SELECT SESSIONTIMEZONE FROM DUAL;

Resulting in ‘+01:00’ for the particular server. On all other servers the result was ‘Europe/Amsterdam’

After adding

EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE = '''||l_time_zone||'''';

in the code:

set serveroutput on size 100000
declare
  lc_FormatMask CONSTANT VARCHAR2(50) := 'YYYYMMDDHH24MISS';
  l_eta_ts      TIMESTAMP(6);
  l_etd_ts      TIMESTAMP(6);
  l_eta_gui     DATE;
  l_etd_gui     DATE;
  l_time_zone   CONSTANT VARCHAR2(30) := 'Europe/Amsterdam';
begin
  EXECUTE IMMEDIATE  'ALTER SESSION SET TIME_ZONE = '''||l_time_zone||'''';

  /* get the EDI strings as TimeStamps */
  select Sys_Extract_UTC(To_Timestamp(to_char(to_date(eta_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
       , Sys_Extract_UTC(To_Timestamp(to_char(to_date(etd_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
  into   l_eta_ts
       , l_etd_ts
  from   EDITABLE;

  /* show what is stored */
  dbms_output.put_line('Eta in: ' || to_char(l_eta_ts, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd in: ' || to_char(l_etd_ts, 'YYYYMMDDHH24MISS'));

  /* conversion used by GUI */
  l_eta_gui := CAST(from_tz(l_eta_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);
  l_etd_gui := CAST(from_tz(l_etd_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);

  /* show what is displayed */
  dbms_output.put_line('Eta gui: ' || to_char(l_eta_gui, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd gui: ' || to_char(l_etd_gui, 'YYYYMMDDHH24MISS'));
end;
/

The proper result was shown:
Eta in: 20180324070000 (UTC-1)
Etd in: 20180326060000 (UTC-2)
Eta gui: 20180324080000
Etd gui: 20180326080000

So, 2 lessons learned :)
-Read the Manual (Chapter 11 page 54 of Oracle® Database SQL Language Reference 11g Release 2):

Note: Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide.

-Make sure all your environments are created and configured the same