Convert java.sql.Timestamp to Java 8 ZonedDateTime?

Question

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?

Solution 1

This seems to work:-

ZonedDateTime.ofInstant(rs.getTimestamp("columnname").toInstant(), ZoneId.of("UTC"))

Solution 2

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). 

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?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  • Java 9 brought some minor features and fixes.
• Java SE 6 and Java SE 7
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android (26+) bundle implementations of the java.time classes.
  • For earlier Android (<26), a process known as API desugaring brings a subset of the java.time functionality not originally built into Android.
    • If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….

Solution 3

Use the following:

rs.getTimestamp("columnName").toInstant()
    .atZone(ZoneId.[appropriate-zone-ID-here])

You need to use a ZoneId appropriate to the region (you may try with ZoneId.systemDefault() for a start).

For more details about the differences between various Java-Time APIs, see this great answer.

Comments

[Ole V.V.]

What is the database column datatype? Can you give an example of a correct value, as shown in an SQL query and then as ZonedDateTime? And then an example of a wrong date, as in the database, with what would have been the correct ZonedDateTime and how the actual result differs? “In some cases, the date is wrong” is — frankly — hopelessly vague.

[Parameswar]

Sorry, this seems to be working: ZonedDateTime.ofInstant(rs.getTimestamp("column").toInstant(‌​), ZoneId.of("UTC"))

[markdsievers]

Holy smokes, thanks for the machete to navigate the Java + JDBC Time thicket!

[mangusta]

getObject(..., Instant.class) gives conversion to class java.time.Instant from timestamp not supported (Java 11)

[Basil Bourque]

@mangusta Yes, I need to fix that. Support for Instant is optional. The JDBC 4.2 spec requires support for OffsetDateTime.