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.

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.

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.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

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.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

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 LocalDate and LocalDateTime, are the best options to map date and timestamp columns.

Insert details about how the information is going to be processed

DOWNLOAD NOW