Skip to content

Daylight Savings Notes

Jump to bottom
Ryan Slominski edited this page Jun 22, 2023 · 31 revisions

Overview

The time accounting app requires saving one or more records per hour during machine operation and an enormous amount of complexity arises due to Daylight Savings. JLab is located in timezone America/New_York, which means once a year there are two hours that have the same wall clock hour reading. For example 1 AM November 4th 2018 is an ambiguous date in our timezone because there are two such hours. The minimum information to disambiguate is to apply either an EST (-5:00) or an EDT (-4:00) qualifier.

How BTM works

We use an Oracle TIMESTAMP WITH LOCAL TIMEZONE datatype to store records keyed by an hour, day, month, and year. For records keyed by shift start hour we can just use DATE or TIMESTAMP since none of the JLab shifts start on an ambiguous hour.

Under the hood the time is stored in Oracle in a binary form such that DATE is smallest time type because of no milliseconds and TIMESTAMP with Local Timezone is equal to TIMESTAMP size-wise, it just has different interface/behavior and assumes the Database Timezone. TIMESTAMP WITH TIMEZONE is biggest type as it actually stores a timezone.

There are four common ways to store time such that ambiguous hours are avoided:

  1. Using a NUMBER datatype and treat it like an EPOCH in UTC as UNIX timestamps do. Apps would be responsible for translation to local time.
  2. Use a DATE (or TIMESTAMP if millis needed) and store as UTC, such that applications are responsible for converting to/from UTC. All Timezones are offsets from UTC and UTC has no Daylight Savings.
  3. Use a TIMESTAMP with TIMEZONE datatype
  4. Use a TIMESTAMP with LOCAL TIMEZONE datatype

Number 4 is a middle ground. It allows passing a time in familiar local time (with TZD qualifier) and instruct Oracle to store it. Example:

alter session set time_zone = 'America/New_York';

INSERT INTO BTM_OWNER.CC_ACC_HOUR(CC_ACC_HOUR_ID, DAY_AND_HOUR, UP_SECONDS, SAD_SECONDS, DOWN_SECONDS, STUDIES_SECONDS, ACC_SECONDS, RESTORE_SECONDS) VALUES (17760, to_timestamp_tz('2018-11-04 01 EDT','YYYY-MM-DD HH24 TZD'), 3600, 0, 0, 0, 0, 0);
INSERT INTO BTM_OWNER.CC_ACC_HOUR(CC_ACC_HOUR_ID, DAY_AND_HOUR, UP_SECONDS, SAD_SECONDS, DOWN_SECONDS, STUDIES_SECONDS, ACC_SECONDS, RESTORE_SECONDS) VALUES (17761, to_timestamp_tz('2018-11-04 01 EST','YYYY-MM-DD HH24 TZD'), 3600, 0, 0, 0, 0, 0);

There are many places that affect time:

  • Database Timezone
  • Database Session Timezone
  • Database OS Timezone
  • Client OS Timezone
  • Explicit Timezone specifiers in SQL statements
  • Explicit Timezone specifiers in application code (Java Date/Calendar, java.sql.Date/Timestamp, JPA/Hibernate APIs)

A Java Date object stores time as an EPOCH like a UNIX timestamp, but does not expose an API to deal with daylight savings. Converting to/from a Java Calendar object allows daylight savings manipulations. The java.time package has a better API, but came out much later after this app was developed. The interface to JPA/Hibernate is a complication, which can be avoided with native SQL statements and inserting records using timestamp with timezone string literals.

If you do try to use JPA/Hibernate you're in for a ride (even a decade later). A few points:

  • Latest JPA (now Jakarta Persistence) still doesn't support timezones. Read the v3.1 spec.
  • Hibernate will normalize all Dates to the JVM TZ when preparing an insert SQL statement string literal, meaning if you want to store Dates in the DB as UTC you can't unless you set your whole JVM TZ to UTC, at least not until recently when a special Hibernate hibernate.jdbc.time_zone property became available.
  • JDBC supports Calendar object on update/insert/select, but it isn't honored in JPA in that it's treated like a Date object and the timezone info is ignored and the JVM timezone is used instead. Hibernate behavior appears to have changed back and forth over the years on this, at one point using a Calendar object to house timestamp info resulting in that TZ being used over the JVM TZ, but the fundamental problem is since the TZ isn't persisted anywhere (JPA doesn't support Timestamp with Timezone datatypes) the read MUST use JVM TZ so to be symmetric the write probably should too.
  • Simply setting all OS/Database/JVM timezones to America/New_York has historically not worked (Hibernate Date insert breaks at ambiguous hour). This appears to be because JPA converts Dates to a String literal for SQL insert statement and this literal lacks TZ Offset info. Java Date and Calendar store time internally as an EPOCH, so if this number was used instead this shouldn't be a problem. Without a EST vs EDT qualifier, the DB (Oracle in our case) will default to the EST Offset (-5:00) I believe. Interestingly reading the wall clock ambiguous hours in with JPA/Hibernate works, and no special native query is needed, which suggests the ResultSet returned includes a representation as an EPOCH, else a String literal that includes the correct offset.
  • With Hibernate there are recent work-arounds, and more work-arounds on the way, but historically the only reliable way to use JPA and honor daylight savings was to not use JPA Temporal types (i.e. use a NUMBER representing an EPOCH instead), else use NATIVE inserts using format strings (sorta defeats the purpose of JPA at that point, I know). You may try to use an Attribute Converter, but the result must be a supported JPA type, so it would help with the EPOCH number case, but not the Date/Calendar/Instant/LocalDate case as those Temporal types all map to java.sql.Timestamp/Date which result in SQL insert string literals without TZ Offset info.

See Also

Clone this wiki locally