Spring JPA Auditing with ZonedDateTime and MySQL

Spring JPA Auditing makes it easy to track data activity. And since we have this cool new DateTime API in Java 8 we might as well use it! Instead of a standard DateTime for Creation and Modification Time we're going to use the new ZonedDateTime class. This can make storing the date time value a little tricky, especially in MySQL. In this post we're going to go over how to store pretty data like below.

To read more about implementing Spring JPA Auditing head over to Petri Kainulainen's blog and read his excellent 2-Part Tutorial on Spring Data JPA Auditing. And while you're there subscribe to Petri's new Newsletter!

Petri uses ZonedDateTime and describes creating an AuditingDateTimeProvider class which implements Spring's DateTimeProvider auditing interface. AuditingDateTimeProvider then uses a DateTimeService implementation to retrieve the current ZonedDateTime.  See Petri's JPA Auditing tutorial for the details.

Adding ZonedDateTime to the Model

We're using the Jadira Usertypes Library to persist the ZonedDateTime values in Hibernate. That looks like this.

@Column(name = "creation_time", nullable = false)
@Type(type = "org.jadira.usertype.dateandtime.threeten.PersistentLocalDateTime")
private ZonedDateTime creationTime;
Then we make sure to add Jadira to our dependencies in Gradle.

compile 'org.jadira.usertype:usertype.extended:3.2.0.GA'

Storing the ZonedDateTime in MySQL or H2Database

This is the fun part, storing the data in MySQL which wasn't in Petri's excellent tutorial. You can store the ZonedDateTime values created by Spring JPA Auditing in binary in both H2 and MySQL (the two data stores supported in NixMash Spring.) Binary isn't my datatype of choice, so we're going to save the ZonedDateTime as TimeStamps. To do that we need to create a ZonedDateTimeConverter class to convert the Entity ZonedDateTime value to the Database Java.sql.Timestamp value.

@Converter(autoApply = true)
public class ZonedDateTimeConverter implements AttributeConverter<java.time.ZonedDateTime, java.sql.Timestamp> {

    public java.sql.Timestamp convertToDatabaseColumn(ZonedDateTime entityValue) {
       return Timestamp.from(entityValue.toInstant());

    public ZonedDateTime convertToEntityAttribute(java.sql.Timestamp databaseValue) {
        LocalDateTime localDateTime = databaseValue.toLocalDateTime();
        return localDateTime.atZone(ZoneId.systemDefault());


The Converter adds the final ingredient to using Java 8 DateTime classes like ZonedDateTime with Spring JPA Auditing and enjoy pretty data in MySQL, H2 and other storage types.

The code in this post can be found in the v0.2.0 branch of NixMash Spring on GitHub.

Posted September 14, 2015 10:36 AM EDT

More Like This Post