How to map Date and Timestamp with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, I’m going to show you what is the best way to map date and timestamp column types when using JPA and Hibernate.

While there are many options to map date and timestamp columns on the Java side, as you will soon see, not all of them are suitable.

Date and Timestamp database columns

Let’s assume we have an user_account table that stores the date when the user has subscribed, and a post table with a published_on column storing the timestamp value when the post got published.

MySQL Date and Timestamp columns

If we’re using MySQL, the subscribed_on column type in the user_account table can be DATE, and the type of the published_on column in the post table can be DATETIME.

The user_account and post tables using MySQL Date and DateTime columns

MySQL also offers a TIMESTAMP column to store date and time information. However, since the maximum value of the TIMESTAMP column is 2038-01-09 03:14:07, it’s usually a better idea to use DATETIME instead.

PostgreSQL Date and Timestamp columns

If we’re using PostgreSQL, the subscribed_on column type in the user_account table can be DATE, and the type of the published_on column in the post table can be TIMESTAMP.

The user_account and post tables using PostgreSQL Date and Timestamp columns

PostgreSQL also offers a TIMESTAMP WITH TIME ZONE column to store date and time information. The TIMESTAMP WITH TIME ZONE column will convert the provided timestamp value to UTC based on the current TIMEZONE setting.

Next, we will see what options we have to map the date and timestamp column types as JPA or Hibernate entity attributes.

Storing timestamp in UTC

As I explained in this article, it’s a very good idea to store the timestamp values in UTC.

First, you need to configure the database server to use the UTC timezone. For example, in PostgreSQL, you can do that by providing the following setting in the postgresql.conf file:

timezone = 'UTC'

In MySQL, you can set this in the my.cnf (e.g., Linux) or my.ini (e.g., Windows) configuration files:

default_time_zone='+00:00'

For MySQL 8, make sure you are using the 8.0.20 Connector/J driver or newer as it fixes a DATE conversion bug.

Second, you need to set the hibernate.jdbc.time_zone Hibernate property to the value of UTC.

This can be done via the persistence.xml configuration file:

<property name="hibernate.jdbc.time_zone" value="UTC"/>

Or, via the Spring Boot application.properties file:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Without providing this property, the JDBC Driver might convert the provided timestamp values from the JVM time zone to the database timezone.

Storing data and timestamp columns with JPA and Hibernate

To map the date column, we have the following options:

  • java.sql.Date
  • java.util.Date
  • LocalDate

To map the timestamp column, we can use one of the following Java types:

  • java.sql.Timestamp
  • java.util.Date
  • LocalDateTime
  • OffsetDateTime
  • ZonedDateTime

Next, we will analyze all these options and see the advantages and disadvantages of each entity attribute mapping.

Mapping Date and Timestamp using java.sql.Date and java.sql.Timestamp

JDBC offers the java.sql.Date and java.sql.Timestamp to map Date and Timestamp columns, so, we can map the subscribed_on and published_on columns using the following JPA and Hibernate entity mappings:

@Column(name = "subscribed_on")
private java.sql.Date subscribedOn;
		
@Column(name = "published_on")
private java.sql.Timestamp publishedOn;

Considering we have the following utility methods:

private final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

private final SimpleDateFormat DATE_TIME_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

private java.sql.Date parseDate(String date) {
    try {
        return new Date(DATE_FORMAT.parse(date).getTime());
    } catch (ParseException e) {
        throw new IllegalArgumentException(e);
    }
}

private java.sql.Timestamp parseTimestamp(String timestamp) {
    try {
        return new Timestamp(DATE_TIME_FORMAT.parse(timestamp).getTime());
    } catch (ParseException e) {
        throw new IllegalArgumentException(e);
    }
}

When persisting the following UserAccount and Post entities:

UserAccount user = new UserAccount()
    .setId(1L)
    .setFirstName("Vlad")
    .setLastName("Mihalcea")
    .setSubscribedOn(
        parseDate("2013-09-29")
    );

Post post = new Post()
    .setId(1L)
    .setTitle("High-Performance Java Persistence")
    .setCreatedBy(user)
    .setPublishedOn(
        parseTimestamp("2020-05-01 12:30:00")
    );

entityManager.persist(user);

entityManager.persist(post);

Hibernate generates the proper SQL INSERT statements:

INSERT INTO user_account (
    first_name, 
    last_name, 
    subscribed_on, 
    id
) 
VALUES (
    'Vlad', 
    'Mihalcea', 
    '2013-09-29', 
    1
)

INSERT INTO post (
    user_account_id, 
    published_on, 
    title, 
    id
) 
VALUES (
    1, 
    '2020-05-01 12:30:00', 
    'High-Performance Java Persistence', 
    1
)

And, when fetching the entire from the database, we can see that the Date and Timestamp values are exactly the ones we persisted:

Post post = entityManager.find(
    Post.class, 1L
);

assertEquals(
    parseTimestamp("2020-05-01 12:30:00"),
    post.getPublishedOn()
);

UserAccount userAccount = post.getCreatedBy();

assertEquals(
    parseDate("2013-09-29"),
    userAccount.getSubscribedOn()
);

While the mapping is straightforward, most applications don’t want to tie their JPA entities to JDBC API classes. So, let’s see what other options we have.

Mapping Date and Timestamp using java.util.Date

Instead of using the JDBC Date and Timestamp classes, we can use the java.util.Date for both the Date and Time column types. To differentiate between these two column types, JPA offers the @Temporal annotation, as illustrated by the following JPA entity attribute mapping:

@Column(name = "subscribed_on")
@Temporal(TemporalType.DATE)
private java.util.Date subscribedOn;

@Column(name = "published_on")
@Temporal(TemporalType.TIMESTAMP)
private java.util.Date publishedOn;

The utility parseDate and parseTimestamp methods don’t require any extra wrapping this time:

private final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

private final SimpleDateFormat DATE_TIME_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

private java.util.Date parseDate(String date) {
    try {
        return DATE_FORMAT.parse(date);
    } catch (ParseException e) {
        throw new IllegalArgumentException(e);
    }
}

private java.util.Date parseTimestamp(String timestamp) {
    try {
        return DATE_TIME_FORMAT.parse(timestamp);
    } catch (ParseException e) {
        throw new IllegalArgumentException(e);
    }
}

Persisting and fetching the UserAccount and Post entities does not change, so it will not be repeated for brevity’s sake.

The advantage of using the java.util.Date is that the data access layer entities are no longer coupled to JDBC API classes. The disadvantage is that we need to provide the @Temporal annotation to instruct the JPA provider about the associated database column type.

Mapping Date and Timestamp using LocalDate and LocalDateTime

As explained in this article, JPA 2.2 adds support for Java 8 Date/Time API.

So, we can map the subscribed_on to a LocalDate and the published_on column to a LocalDateTime:

@Column(name = "subscribed_on")
private LocalDate subscribedOn;

@Column(name = "published_on")
private LocalDateTime publishedOn;

Compared to java.util.Date or its JDBC counterparts, the Java Date/Time API offers is much easier to use and doesn’t require any utility methods as the provided factory methods are very easy to use:

UserAccount user = new UserAccount()
    .setId(1L)
    .setFirstName("Vlad")
    .setLastName("Mihalcea")
    .setSubscribedOn(
        LocalDate.of(
            2013, 9, 29
        )
    );

Post post = new Post()
    .setId(1L)
    .setTitle("High-Performance Java Persistence")
    .setCreatedBy(user)
    .setPublishedOn(
        LocalDateTime.of(
            2020, 5, 1,
            12, 30, 0
        )
    );

entityManager.persist(user);

entityManager.persist(post);

The SQL INSERT queries are identical to the ones presented before. And, the entities are properly fetched from the database too:

Post post = entityManager.find(
    Post.class, 1L
);

assertEquals(
    LocalDateTime.of(
        2020, 5, 1,
        12, 30, 0
    ),
    post.getPublishedOn()
);

UserAccount userAccount = post.getCreatedBy();

assertEquals(
    LocalDate.of(
        2013, 9, 29
    ),
    userAccount.getSubscribedOn()
);

Mapping Timestamp using OffsetDateTime

You can also use the Java 8 OffsetDateTime to map the published_on column:

@Column(name = "published_on")
private OffsetDateTime publishedOn;

However, when persisting the Post entity:

Post post = new Post()
    .setId(1L)
    .setTitle("High-Performance Java Persistence")
    .setCreatedBy(user)
    .setPublishedOn(
        LocalDateTime.of(
            2020, 5, 1,
            12, 30, 0
        ).atOffset(ZoneOffset.UTC)
    );

entityManager.persist(post);

We can see that Hibernate converted the timestamp according to our local time zone:

INSERT INTO post (
    user_account_id, 
    published_on, 
    title, 
    id
) 
VALUES (
    1, 
    '2020-05-01 15:30:00.0', 
    'High-Performance Java Persistence', 
    1
)

This is because the OffsetDateTimeJavaDescriptor wraps the provided OffsetDateTime to a Timestamp, like this:

return (X) Timestamp.from( offsetDateTime.toInstant() );

And, when reading it from the database it converts it to the local time zone:

return OffsetDateTime.ofInstant( ts.toInstant(), ZoneId.systemDefault() );

So, the only way it works if the system time zone is used:

Post post = new Post()
    .setId(1L)
    .setTitle("High-Performance Java Persistence")
    .setCreatedBy(user)
    .setPublishedOn(
        LocalDateTime.of(
            2020, 5, 1,
            12, 30, 0
        ).atOffset(
            ZoneOffset.systemDefault()
                .getRules()
                .getOffset(LocalDateTime.now())
        )
    );

When persisting an OffsetDateTime entity attribute, Hibernate does not store the offset separately, hence the local offset is used. For this reason, the OffsetDateTime is not very useful, and it can be replaced by a LocalDateTime instead.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Mapping Timestamp using ZonedDateTime

You can also use the Java 8 ZonedDateTime to map the published_on column:

@Column(name = "published_on")
private ZonedDateTime publishedOn;

However, just like it was the case of OffsetDateTime, only the system time zone works when persisting and fetching entity attributes.

So, this is how you need to persist the ZonedDateTime:

Post post = new Post()
    .setId(1L)
    .setTitle("High-Performance Java Persistence")
    .setCreatedBy(user)
    .setPublishedOn(
        LocalDateTime.of(
            2020, 5, 1,
            12, 30, 0
        ).atZone(ZoneId.systemDefault())
    );

In order to be able to fetch it from the database:

assertEquals(
    LocalDateTime.of(
        2020, 5, 1,
        12, 30, 0
    ).atZone(ZoneId.systemDefault()),
    post.getPublishedOn()
);

When persisting a ZonedDateTime entity attribute, Hibernate does not store the time zone separately, hence the local offset is used. For this reason, the ZonedDateTime is not very useful, and it can be replaced by a LocalDateTime instead.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

When using JPA and Hibernate, the java.util.Date, as well as LocaDate and LocaDateTime are the best options to map date and timestamp columns.

Transactions and Concurrency Control eBook

6 Comments on “How to map Date and Timestamp with JPA and Hibernate

  1. One gotcha: by using this:

    @Temporal(TemporalType.TIMESTAMP)
    private java.util.Date publishedOn;
    

    at runtime, the actual type of publishedOn will be type java.sql.Timestamp, which leads to unexpected results when using the after(), before() and compareTo() methods.

  2. I see it happen often and have never seen a negative consequence, but SimpleDateFormat shouldn’t be instantiated directly according to the documentation. What I do is instantiate DateFormat with the static factory methods and if the returned instance is a SimpleDateFormat, I then will apply a format.

      • So out of curiosity and seeking to understand the difference of SimpleDateFormat() versus DateFormat.getInstance() and applying a pattern if the instance is a SimpleDateFormat, I looked at the OpenJDK source to get a better idea of the difference in those patterns. I believe the issue is that SimpleDateFormat directly is fine for a locale provided by the JRE. If you need to be based on locale information for a locale not provided by the JRE (you’re using a LocaleServiceProvider), DateFormat will consult the LocaleServiceProvider and return something ‘better’ than what’s provided by the JRE when possible which might not be a SimpleDateFormat. If there’s no LocaleServiceProvider in play, it just uses a SimpleDateFormat directly behind the scenes in all cases. So in practice at least based on the OpenJDK 7 implementations, that seems like the implementation-dependent difference between the two approaches if you’re interested.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.