March 01, 2015 / by Jörg Rathlev / Software engineer / @

Using the new java.time API with PostgreSQL

This article gives you a short overview of how to use Java 8’s new date and time classes with JDBC. Example code is included for storing and retrieving timestamps in a relational database.

If you’re a Java developer, you probably already know that Java 8 ships with a new date and time API. There is also a backport available for Java 6 and 7. One of the specification leads for the new date and time API was Stephen Colebourne, who previously designed the Joda Time library for Java.

Lots of articles have already been published that explain the benefits of using the new date and time API, so I won’t repeat them here. In practice, the main drawback is probably that you cannot use the new API everywhere simply because you have to interface with libraries that still use the date and time classes from earlier versions of Java. One of those libraries, and the subject of this blog post, is JDBC.

JDBC 4.2

Java 8 includes JDBC version 4.2. In this version of JDBC, the familiar classes java.sql.Timestamp, java.sql.Date, and java.sql.Time have been extended with new methods to convert to and from the new date and time types of java.time. For example, an SQL timestamp value can be converted from an instant value by using the static factory method Timestamp#from(Instant), and converted to an instant by using the method toInstant().

If you’re using a JDBC 4.2-compliant driver, the driver should automatically map the new types java.time.LocalDateTime, java.time.OffsetDateTime, and so on to their respective types in SQL. However, at the time of writing, the JDBC drivers at least for PostgreSQL and MySQL do not yet support JDBC 4.2.

Example: Storing timestamps in a database

Let’s now look at a simple example: storing timestamps in the database. The simplest way to create a timestamp in Java with the new date and time API is to use the new Instant type:

Instant eventOccurred = Instant.now();

To store this value in a database, convert it to a java.sql.Timestamp first. This example also uses a Calendar object to explicitly store the timestamp in UTC time. This is important if you use a database that stores time zones along with the values and want that time zone to be UTC, or if you’re using the SQL TIMESTAMP (without time zone) type and want to ensure that the timestamp is stored in UTC time instead of in the JVM’s time zone.

Calendar utc = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
PreparedStatement ps = ...
ps.setTimestamp(1, Timestamp.from(eventOccurred), utc);

Although dealing with time zones is not directly related to the new date and time APIs, using the new classes can actually improve the readability of the above code. With a JDBC driver that supports version 4.2 of the JDBC specification, you should instead be able to use the following code, which does not require a Calendar object (note that I have not tested this):

PreparedStatement ps = ...
ps.setObject(1, eventOccurred.atOffset(ZoneOffset.UTC));

When reading from the database, use the new toInstant method of Timestamp to convert the value back into an Instant. You can then use the new date and time API for the actual application logic:

ResultSet rs = ...
Instant eventOccurred = rs.getTimestamp('column_name', utc).toInstant();

// for example:
Duration d = Duration.between(eventOccurred, Instant.now());
System.out.println("Stored event occurred " + d.toMillis() + " milliseconds ago.");

Summary

Java 8’s new date and time API is huge improvement over the date and time classes previously included in the JDK. The old date and time classes have been extended with sensible methods to convert to and from the new types. Until JDBC drivers support the new date and time types directly, explicit conversion is still required.