Saving date in UTC in Oracle Timestamp column

Question

I have a requirement to save the current UTC date with time in an Oracle column of type TIMESTAMP WITH TIMEZONE. this is from a Spring Boot service with JPA and Hibernate

I have enabled the following in my application yml

  jpa:
    hibernate:
      ddl-auto: none
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect
        jdbc:
          time_zone: UTC

and the Entity class field looks like

@Column(name = "last_user_edit_date", columnDefinition = "TIMESTAMP WITH TIME ZONE")
@JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSZ")
private ZonedDateTime lastUserEditDate;

While setting date I am using

obj.setLastUserEditDate(ZonedDateTime.of(LocalDateTime.now(), ZoneId.of("UTC")));

The above is working fine with respect to the actual date value. The only problem is in the database it is saving the UTC time but mentions MST (my local timezone) as the timezone. For eg a value saved is

12-SEP-19 09.50.53.820000000 PM AMERICA/DENVER

Here the 9.50 PM is actually the UTC time but the timezone comes as AMERICA/DENVER. What i want is

12-SEP-19 09.50.53.820000000 PM UTC

How can i achieve this Spring JPA and with Java 8 classes?

Thanks

Answer 1

LocalDateTime is the wrong class

The LocalDateTime class is not capable of tracking a moment in time. I cannot imagine a scenario where calling LocalDateTime.now() makes sense. Read the Javadoc before using a class.

Track a moment: Instant, OffsetDateTime, ZonedDateTime

To track a moment, use Instant (always in UTC), OffsetDateTime (a moment with an offset-from-UTC), or ZonedDateTime (a moment as seen in a particular region).

Oddly, JDBC 4.2 requires support for OffsetDateTime yet leaves the most common two classes, Instant & ZonedDateTime, optional.

So, to capture the current moment in UTC for JDBC work:

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Or the longer:

Instant instant = instant.now() ;  // Capture current moment in UTC.
OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Send to database:

myPreparedStatement.setObject( … , odt ) ;

Retrieve from database:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

JPA

I do not use JPA. But it looks this Question has you covered, JPA Storing OffsetDateTime with ZoneOffset. And see this post, What’s new in JPA 2.2 – Java 8 Date and Time Types.

Other time zones & offsets

only problem is in the database it is saving the UTC time but mentions MST

This documentation for Oracle Database seems to say that TIMESTAMP WITH TIME ZONE type does record the incoming data’s time zone or offset-from-UTC. Some other databases such as Postgres adjust incoming values to UTC (an offset of zero hours-minutes-seconds).

To get UTC, retrieve the OffsetDateTime as seen above, and call toInstant method to produce a Instant object which is always in UTC. Or produce another OffsetDateTime that is definitely in UTC:

OffsetDateTime odtUtc = odt.withOffsetSameInstant​( ZoneOffset.UTC ) ;

---