Convert java.sql.Timestamp to Java 8 ZonedDateTime?

Parameswar picture Parameswar · Jul 16, 2018 · Viewed 14.2k times · Source

Migrating Joda time to Java 8

Joda:

UserObject user = new UserObject()
user.setCreatedAt(new DateTime(rs.getTimestamp("columnName")));`

Migrating to Java 8

This is my code; it does compile; I am doubtful if it works:

ZonedDateTime.ofInstant(rs.getTimestamp("columnName").toLocalDateTime().toInstant(ZoneOffset.UTC),ZoneId.of("UTC")));

In some cases, the date is wrong. Any advice?

Answer

Basil Bourque picture Basil Bourque · Jul 16, 2018

tl;dr

To track a moment in history, use Instant as the type of your class member variable. Specifically, this moment is seen as a date and time-of-day in UTC.

public class UserObject() {
    Instant createdAt ;
    …
    public void setCreatedAt( Instant instantArg ) {
        this.createdAt = instantArg ;
    {
}

Usage, capturing the current moment.

UserObject user = new UserObject() ;
user.setCreatedAt( Instant.now() ) ;

Usage, populating value from database.

UserObject user = new UserObject() ;
Instant instant = myResultSet.getObject( "when_created" , Instant.class ) ;
user.setCreatedAt( instant ) ;

JDBC 4.2 does not require support for Instant (a moment in UTC). If your driver does not support that class, switch to OffsetDateTime which is required.

UserObject user = new UserObject() ;
OffsetDateTime odt = myResultSet.getObject( "when_created" , OffsetDateTime.class ) ;  
user.setCreatedAt( odt.toInstant() ) ;  // Convert from an `OffsetDateTime` (for any offset-from-UTC) to `Instant` (always in UTC). 

Table of date-time types in Java (both modern and legacy) and in standard SQL.

Present to the user, localized for the user-interface.

user               // Your business object.
.getCreatedAt()    // Returns a `Instant` object.
.atZone(           // Adjust from UTC to a time zone. Same moment, same point on the timeline, different wall-clock time.
    ZoneId.of( "Pacific/Auckland" )  // Specify the user’s desired/expected time zone.
)                  // Returns a `ZonedDateTime` object. 
.format(           // Generate a `String` representing the value of date-time object.
    DateTimeFormatter.ofLocalizedDateTime(
        FormatStyle.FULL   // Specify how long or abbreviated the string.
    )
    .withLocale(   // Specify `Locale` to determine human language and cultural norms for localization.
        Locale.CANADA_FRENCH 
    )
)                  // Returns a `String`.

Notice that Locale has nothing to do with time zone, an orthogonal issue. The code above might be for a business person from Québec who is traveling in New Zealand. She wants to see the wall-clock time used by the kiwis around her, but she prefers to read its textual display in her native French. Both time zone and locale are issues best left to presentation only; generally best to use UTC in the rest of your code. Thus, we defined our member variable createdAt as an Instant, with Instant always being in UTC by definition.

Avoid java.sql.Timestamp

  • The java.sql.Timestamp, along with java.sql.Date, java.util.Date, and Calendar are all part of the terribly troublesome old date-time classes that were supplanted years ago by the java.time classes.
  • Joda-Time too is now supplanted by the java.time classes, as stated in the front page of the project’s site.

java.time

As of JDBC 4.2 and later, we can directly exchange java.time objects with the database.

Instant

Send the current moment to the database using Instant class. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).

Instant instant = Instant.now() ;  // Capture the current moment in UTC.
myPreparedStatement.setObject( … , instant ) ;

And retrieval.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

ZonedDateTime

To see that same moment through the lens of the wall-clock time used by the people of a particular region (a time zone), apply a ZoneId to get a ZonedDateTime.

ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

LocalDateTime is not a moment

.toLocalDateTime().

Never involve LocalDateTime class when representing a specific moment in time. The class purposely lacks any concept of time zone or offset-from-UTC. As such, it cannot represent a moment, is not a point on the timeline. It is a vague idea about potential moments along a range of about 26-27 hours (the range of time zones).


About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?