On Oracle: Oracle Database and Java Datetime Conversion Differences across Daylight Saving Time (DST)

Following the long discussions of Blog: Oracle Datetime (1) - Concepts, we also observed the difference of Datetime conversion on Daylight Saving Time (DST) between Java and Sql.

For example, for Paris Winter to Summer DST time switch, on Sunday 31 March 2019, 02:00:00 clocks were turned forward 1 hour to 03:00:00.

In this Blog, we made two tests to demonstrate the difference of Datetime arithmetic across DST turning point, one is in Java, another is in Sql.

Note: All tests are done in Oracle 12c, 18c, 19c and Java 8.

1. Java Test

Create following Java class:


------------------ Java Code -----------------

import java.time.LocalDateTime;
import java.time.Month;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;

public class TestDST
{
   private static final String DATE_FORMAT = "yyyy-MMMM-dd kk:mm:ss VV O";
   private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern(DATE_FORMAT);
    
   public static void main(String[] args)
   {
      ZoneId timeZone = ZoneId.of("Europe/Paris");    // timezone
      LocalDateTime localDT = LocalDateTime.of(2019, Month.APRIL, 01, 02, 11, 33);  //2019-04-01 02:11:33
      
      for (int h = 1; h <= 3; h++) {
        localDT = LocalDateTime.of(2019, Month.APRIL, 01, h, 11, 33);  
        ZonedDateTime zDT = localDT.atZone(timeZone);  //Zoned date time
        System.out.println("(" + formatter.format(zDT) + " - 1 Day) = " + formatter.format(zDT.minusDays(1))); 
        System.out.println("(" + formatter.format(zDT) + " - 2 Day) = " + formatter.format(zDT.minusDays(2)));
      }
      System.out.println();
      for (int h = 1; h <= 3; h++) {
        localDT = LocalDateTime.of(2019, Month.MARCH, 30, h, 11, 33);  
        ZonedDateTime zDT = localDT.atZone(timeZone);  //Zoned date time
        System.out.println("(" + formatter.format(zDT) + " + 1 Day) = " + formatter.format(zDT.plusDays(1))); 
        System.out.println("(" + formatter.format(zDT) + " + 2 Day) = " + formatter.format(zDT.plusDays(2)));
      }        
   }
}

Compile and then run it:


javac -cp . TestDST.java

java -cp . TestDST

Here the Output for Datetime minus and plus of 1 or 2 days in Java.


$ > java -cp . TestDST

--------------------------------- Datetime minus ---------------------------------
(2019-April-01 01:11:33 Europe/Paris GMT+2 - 1 Day) = 2019-March-31 01:11:33 Europe/Paris GMT+1
(2019-April-01 01:11:33 Europe/Paris GMT+2 - 2 Day) = 2019-March-30 01:11:33 Europe/Paris GMT+1
(2019-April-01 02:11:33 Europe/Paris GMT+2 - 1 Day) = 2019-March-31 03:11:33 Europe/Paris GMT+2
(2019-April-01 02:11:33 Europe/Paris GMT+2 - 2 Day) = 2019-March-30 02:11:33 Europe/Paris GMT+1
(2019-April-01 03:11:33 Europe/Paris GMT+2 - 1 Day) = 2019-March-31 03:11:33 Europe/Paris GMT+2
(2019-April-01 03:11:33 Europe/Paris GMT+2 - 2 Day) = 2019-March-30 03:11:33 Europe/Paris GMT+1

--------------------------------- Datetime plus ---------------------------------
(2019-March-30 01:11:33 Europe/Paris GMT+1 + 1 Day) = 2019-March-31 01:11:33 Europe/Paris GMT+1
(2019-March-30 01:11:33 Europe/Paris GMT+1 + 2 Day) = 2019-April-01 01:11:33 Europe/Paris GMT+2
(2019-March-30 02:11:33 Europe/Paris GMT+1 + 1 Day) = 2019-March-31 03:11:33 Europe/Paris GMT+2
(2019-March-30 02:11:33 Europe/Paris GMT+1 + 2 Day) = 2019-April-01 02:11:33 Europe/Paris GMT+2
(2019-March-30 03:11:33 Europe/Paris GMT+1 + 1 Day) = 2019-March-31 03:11:33 Europe/Paris GMT+2
(2019-March-30 03:11:33 Europe/Paris GMT+1 + 2 Day) = 2019-April-01 03:11:33 Europe/Paris GMT+2

2. Sql Test

Here the test and output for the identical Datetime minus and plus of 1 or 2 days in Oracle Sql.


Sql > column datetime format a100
Sql > alter session set time_zone = 'Europe/Paris';

Sql > with base as (select timestamp'2019-04-01 01:11:00 Europe/Paris' v from dual)
          ,diff as (select level-1 v from dual connect by level <= 3)
      select '('||(base.v+numtodsinterval(hh.v, 'hour'))||' - '||dd.v||' day) = '||
              ((base.v+numtodsinterval(hh.v, 'hour')) - numtodsinterval(dd.v, 'day')) datetime 
        from base, diff hh, diff dd
      where dd.v > 0
      order by base.v, hh.v, dd.v; 

--------------------------------- Datetime minus ---------------------------------
(01-APR-2019 01:11:00 EUROPE/PARIS - 1 day) = 31-MAR-2019 00:11:00 EUROPE/PARIS
(01-APR-2019 01:11:00 EUROPE/PARIS - 2 day) = 30-MAR-2019 00:11:00 EUROPE/PARIS
(01-APR-2019 02:11:00 EUROPE/PARIS - 1 day) = 31-MAR-2019 01:11:00 EUROPE/PARIS
(01-APR-2019 02:11:00 EUROPE/PARIS - 2 day) = 30-MAR-2019 01:11:00 EUROPE/PARIS
(01-APR-2019 03:11:00 EUROPE/PARIS - 1 day) = 31-MAR-2019 03:11:00 EUROPE/PARIS
(01-APR-2019 03:11:00 EUROPE/PARIS - 2 day) = 30-MAR-2019 02:11:00 EUROPE/PARIS


Sql > with base as (select timestamp'2019-03-30 01:11:00 Europe/Paris' v from dual)
          ,diff as (select level-1 v from dual connect by level <= 3)
      select '('||(base.v+numtodsinterval(hh.v, 'hour'))||' + '||dd.v||' day) = '||
              ((base.v+numtodsinterval(hh.v, 'hour')) + numtodsinterval(dd.v, 'day')) datetime 
        from base, diff hh, diff dd
      where dd.v > 0
      order by base.v, hh.v, dd.v; 

--------------------------------- Datetime plus ---------------------------------
(30-MAR-2019 01:11:00 EUROPE/PARIS + 1 day) = 31-MAR-2019 01:11:00 EUROPE/PARIS
(30-MAR-2019 01:11:00 EUROPE/PARIS + 2 day) = 01-APR-2019 02:11:00 EUROPE/PARIS
(30-MAR-2019 02:11:00 EUROPE/PARIS + 1 day) = 31-MAR-2019 03:11:00 EUROPE/PARIS
(30-MAR-2019 02:11:00 EUROPE/PARIS + 2 day) = 01-APR-2019 03:11:00 EUROPE/PARIS
(30-MAR-2019 03:11:00 EUROPE/PARIS + 1 day) = 31-MAR-2019 04:11:00 EUROPE/PARIS
(30-MAR-2019 03:11:00 EUROPE/PARIS + 2 day) = 01-APR-2019 04:11:00 EUROPE/PARIS

3. Java vs. Sql

From above output, we can see:

3.1. Java Arithmetic

Java is trying to maintain Datetime literal string value as much as possible. for example,

  (2019-April-01 01:11:33 Europe/Paris GMT+2 - 1 Day) = 2019-March-31 01:11:33 Europe/Paris GMT+1

it only shift one day back, even though the absolute time

  from 2019-March-31 01:11:33 Europe/Paris GMT+1
  to   2019-April-01 01:11:33 Europe/Paris GMT+2

is 23 hours, not one full day of 24 hours. If Datetime value not exists in that timezone, round to the nearest higher value (Positive Infinity). for example,

  (2019-April-01 02:11:33 Europe/Paris GMT+2 - 1 Day) = 2019-March-31 03:11:33 Europe/Paris GMT+2

since "2019-March-31 02:11:33 Europe/Paris GMT+2" does not exist.

3.2. Sql Arithmetic

Sql performs strict 24 hours per day calculation. For example,

  (01-APR-2019 01:11:00 EUROPE/PARIS - 1 day) = 31-MAR-2019 00:11:00 EUROPE/PARIS

If Datetime value does not exist in that timezone, for example, to minus one day, it maps to exact timestamp 24 hours ago.

  (01-APR-2019 02:11:00 EUROPE/PARIS - 1 day) = 31-MAR-2019 01:11:00 EUROPE/PARIS

3.3. Result Differences

Now we end up with different result between Java and Sql, the first one even in different Time Zone (GMT+2 vs. GMT+1).


Java: (2019-April-01 02:11:33 Europe/Paris GMT+2 - 1 Day) = 2019-March-31 03:11:33 Europe/Paris GMT+2
Sql:  (  01-APR-2019 02:11:00 EUROPE/PARIS - 1 day)       =   31-MAR-2019 01:11:00 EUROPE/PARIS

Java: (2019-April-01 01:11:33 Europe/Paris GMT+2 - 2 Day) = 2019-March-30 01:11:33 Europe/Paris GMT+1
Sql:  (  01-APR-2019 01:11:00 EUROPE/PARIS - 2 day)       =   30-MAR-2019 00:11:00 EUROPE/PARIS

Monday, December 23, 2019

Oracle Database and Java Datetime Conversion Differences across Daylight Saving Time (DST)