Oracle: Summer Time & Daylight Savings Time

Background

For a couple of years, I have worked everyday with a team in Belgium. Each fall and each spring, the EU and US team must yet again learn that we flip between Summer Time and Winter Time or Day Light Savings Time and Standard Time. Certainly, there are tons of easy ways to discover which day on the calendar we swap. But, in our data, scheduling appointments look funny.

The application stores dates as “timestamp with local time zone” and therefore all date/time are stored in our tables using UTC (“Zulu”, GMT). As I was building a feature that stored appointments and available timeslots for staff, I observed that suddenly the “avaliable” slots shifted from 8 am to 7am. The answer became obvious. I asked my code to schedule Mon, Wed, Thu as a “available” from 0900 to 1700. When stored as dates in UTC, part of the year landed on 0800 and part landed at 0700 demonstrating either a 1 hour shift or a 2 hour shift from UTC.

Hey, Oracle did all of that for me internally and natively. The logic I was building works, but I thought, if Oracle knows the date that each region changes from Winter Time to Summer Time, then I could query that. Always easier (so I think) to ask my database something than the internet. Especially, now that some internet answers hallucinate and the new AI-drive error rate varies between 3% and 30%.

But how? While I seems perfectly obvious that Oracle knows and “stores” each time-change date, I could not find queries that tell me those dates.

Summer Time/Winter Time Date Change with PLSQL

There are at most 2 dates on the annual calendar when we change time zone (no, we don’t change time zones while standing still). But technically we do change time zone because the offset from the standard time changes. And the name of our time zone can change too. US/Eastern flip-flops between EDT and EST (eastern daylight savings time and eastern standard time). Frankly, I never know which I am in.

Of course, a lot of places on the planet do not undertake this arbitrary time shift. And some businesses don’t adjust either. I’ve worked on farms that keep 1 clock all year. The animals and chores need doing regardless of “government time”. And when I worked with FedEx on international freight, that global schedule of international flights did not shift spring and fall.

Therefore, we have regions that do shift and regions that do not shift. And each government seems to pick their own date for shifting.

How do we find these dates from inside of Oracle?

Time Change Date

When querying data, we need to know how to identify our target. Our target is find the day on the calendar where the offset from UTC is X on one day and Y on the next day.

Ground Works & Foundation

In the list tough lessons with Oracle are:

• null
• boolean (seriously a data type defined as having 2 states technically has 3)
• Dates, time, timestamp, timestamp with time zone

And I am ignoring null and boolean today (but I do love them dearly).

Today the focus is date, timestamp, and timestamp with time zone. First, each are technically different data types. One must convert between them typically with the cast() function.

Second, your NLS session settings matter a significantly. Because date, timestamp, and timestamp with time zones are all literally different, I suggest setting NLS values so that you have better visibility into the difference. I certainly don’t care what your settings are and these only help in seeing the differences:

alter SESSION set NLS_DATE_FORMAT = 'DD-MON-RR HH24.MI'; 
alter SESSION set NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24MI';
alter SESSION set NLS_TIMESTAMP_TZ_FORMAT = 'YYYYMMDD HH24:MI:SS TZR';

With the format differences, you’ll have an easier time seeing the differences in the data types.

Our server is in Ireland. I am in US/Eastern. The client is in Belgium with the CET time zone. I do NOT want to set my session time zone. I need that to be controlled from the keyboard or data. I want precision in my procedure calls.

Query Every Date in a Year

There are a couple of ways of generating lists of numbers with a query. There are a couple of cool ways to generate lists of sequential numbers. They are basically the same.

rownum

select 
	rownum r
from dual
connect by rownum <= 365

level

select
	level lvl
from dual
connect by level <= 365;

Now add your sequence to the first day of a given year with the following:

select
  to_date('01-JAN-' || :year, 'DD-MON-YYYY')  + r - 1 d1
from 
  (
    select 
      rownum r
    from dual
    connect by rownum <= 365
  )
;	

Now you have a list of every day in a year (or minutes in an hour, or hours in a day, or what ever you need an ordinal list of).

Oracle Query to find the time zone offset from UTC

I am going to start with just one date, and by pure planning and 30 seconds of research, I picked 12-MAR-2023 given that is the date that the US changed from winter time to summer time in 2023. (can someone explain to me how we are saving daily light by shifting the numbers on a clock? I never know if I am saving that day light in the winter or summer and even with “standard” time 1200 is not solar noon, but that’s got nothing to do with Oracle).

I am not using NLS session state given we want to be explicit in our declaration of time zone.

with d as 
  (
  select
    to_date('12-MAR-2023','DD-MON-YYYY') ate
  from dual
  )	
 select 
  d.ate,
  cast(d.ate as timestamp) at time zone 'US/Eastern' local_time,
  cast(cast(d.ate + 1 as timestamp) at time zone 'US/Eastern' as date) local_time_next_day,
  cast(d.ate as timestamp) at time zone 'UTC' utc_time,
  cast(cast(d.ate + 1 as timestamp) at time zone 'UTC' as date) utc_time_next_day
 from d;

My results look like this:

Given the differences in NLS formatting you can tell which data type is which:

Make A Mistake, on purpose

There is a few things to observe in the query below. First, I landed some data as dates and some as timestamps with time zone. While the data look identical, and the queries are confusingly similar, the results differ. Here is a query that does not generate the results we need (a “mistake”):

with d as
  (
  select 
    to_date('12-MAR-2023 0300','DD-MON-YYYY HH24MI') ate
  from dual
  )
select
  (local_time - utc_time) * 24 tz_offset1,
  (local_time2 - utc_time2) * 24 tz_offset2
from 
(
  select 
    cast(to_date('12-MAR-2023 0300','DD-MON-YYYY HH24MI') as timestamp) at time zone 'US/Eastern' local_time,
    cast(cast(to_date('12-MAR-2023 0300','DD-MON-YYYY HH24MI') as timestamp) at time zone 'US/Eastern' as date) local_time2,
    cast(to_date('12-MAR-2023 0300','DD-MON-YYYY HH24MI') as timestamp) at time zone 'UTC' utc_time,
    cast(cast(to_date('12-MAR-2023 0300','DD-MON-YYYY HH24MI') as timestamp) at time zone 'UTC' as date) utc_time2
  from dual
)
;

In the first case, the offset is zero and in the second case the offset is -4. Both are correct. Oracle did not make a mistake. But because the timestamp with time zone data type includes the time zone, there is no offset. The offset is visible with the date data type.

Create Query for Offset using Date

with d as 
(
  select
    to_date('12-MAR-2023','DD-MON-YYYY') ate
  from dual
)	
select 
  d.ate,
  cast(cast(d.ate as timestamp) at time zone 'US/Eastern' as date) local_time,
  cast(cast(d.ate + 1 as timestamp) at time zone 'US/Eastern' as date) local_time_next_day,
  cast(cast(d.ate as timestamp) at time zone 'UTC' as date) utc_time,
  cast(cast(d.ate + 1 as timestamp) at time zone 'UTC' as date) utc_time_next_day
from d;

The offset from UTC is starting to be visible:

The delta for US/Eastern to UTC is 5hrs on 12-MAR-2023 and it is 4hrs the next day.

Make a refinement to target the math show less of the intermediate data.

select

  local_time dte,
  (local_time - utc_time) * 24 tz_offset,
  (local_time_next_day - utc_time_next_day) * 24 tz_offset_next_day
from 
(
  with d as 
  (
    select
      to_date('12-MAR-2023','DD-MON-YYYY') ate
    from dual
  )	
  select 
    d.ate,
    cast(cast(d.ate as timestamp) at time zone 'US/Eastern' as date) local_time,
    cast(cast(d.ate + 1 as timestamp) at time zone 'US/Eastern' as date) local_time_next_day,
    cast(cast(d.ate as timestamp) at time zone 'UTC' as date) utc_time,
    cast(cast(d.ate + 1 as timestamp) at time zone 'UTC' as date) utc_time_next_day
  from d
)
;

These results show that between this date and the day that follows, the offset from UTC has shifted.

I have proven that I can find the target. When I shift the dates around, I see expected results.

Put it together – Search a year

Ok, I through in some other steps and functions such as lead and a substitution. Also note that I changed time zone to 'CET' (Central Europe Time). For CET, the time change came on:

• 25-MAR-2023
• 28-OCT-2023

For 'US/Eastern', the results are:

• 12-MAR-2023
• 05-NOV-2023

select
  dte,
  tz_offset,
  next_day_tz_offset
from 
  (
    select
      local_time2 dte,
      (local_time2 - utc_time2) * 24 tz_offset,
      (next_day - next_day_utc) * 24 next_day_tz_offset
    from 
    (
        with d as 
        (        
          select
            d1 ate,
            lead(d1) over (order by d1) ate_after
          from 
            (
              select
                to_date('01-JAN-' || :year, 'DD-MON-YYYY')  + r - 1 d1
              from 
              (
                select 
                  rownum r
                from dual
                connect by rownum <= 365
               )
            )
          )
      select 
        d.ate,
        cast(cast(d.ate as timestamp) at time zone 'CET' as date) local_time2,
        cast(cast(d.ate_after as timestamp) at time zone 'CET' as date) next_day,
        cast(cast(d.ate as timestamp) at time zone 'UTC' as date) utc_time2,
        cast(cast(d.ate_after as timestamp) at time zone 'UTC' as date) next_day_utc
      from d
    )
  )
where tz_offset <> next_day_tz_offset
;

• Category: APEX, plsql
• Tag: Oracle Daylight Savings Time, Oracle Standard Time, Oracle Summer Time, Oracle Winter Time