How to map a TIMESTAMP column to a ZonedDateTime JPA entity property?

Question

I'm using Spring data jpa and mariadb latest version, and MariaDB 10.3.16

+--- org.springframework.boot:spring-boot-starter-data-jpa -> 2.1.5.RELEASE
...
|    +--- org.springframework.boot:spring-boot-starter-jdbc:2.1.5.RELEASE
...
|    +--- org.hibernate:hibernate-core:5.3.10.Final

This is my Entity:

@Entity
@Data
@Table
@NoArgsConstructor
@AllArgsConstructor
public class Note {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Integer id;

    @Column
    private String gsn;

    @Column
    @Enumerated(EnumType.STRING)
    private NoteType type;

    @Column
    private String text;

    @Column
    private ZonedDateTime scheduleDt;

    @Column
    @CreationTimestamp
    private Instant createDt;

    @Column
    @UpdateTimestamp
    private ZonedDateTime updateDt;
}

When I persist my entity, Hibernate tries to save ZonedDateTime member as DATETIME column. But I want to use TIMESTAMP column instead of DATETIME column.

This is create DDL, what I see from log.

create table `note` (`id` integer not null, `create_dt` datetime,
    `gsn` varchar(255), `schedule_dt` datetime, `text` varchar(255),
    `type` varchar(255), `update_dt` datetime, primary key (`id`)) 
  engine=MyISAM

Here create_dt, schedule_dt, update_dt is created as datetime column type, what is not I wanted. (I don't like MyISAM, too).

How can I fix it?

---

Added because comment cannot express ddl.

When I use columnDefinition attribute, generated ddl is ...

create table `note` (`id` integer not null, `create_dt` datetime,
    `gsn` varchar(255), `schedule_dt` datetime, `text` varchar(255),
    `type` varchar(255), `update_dt` `TIMESTAMP`, primary key (`id`)) 

engine=MyISAM

There is unrequired '`' around TIMESTAMP.

Donohue

Not really relaed, but why use a ZonedDateTime rather than an Instant or a LocalDateTime? The timestamp won't store the timezone stored in your ZonedDateTime anywhere, and you thus won't be able to get back a ZonedDateTime with the original time zone. – Neiman

@JB Yes, you're right. Timezone is not recoverable. Anyway Instant, LocalDateTime do not work, too. Generated DDL uses DATETIME colume type. – Donohue

@galex: your inline code formatting changes are fine, but software names and versions are not made more readable by putting them in bold. In general this is not necessary. – Photoneutron

ok thanks for the advice, im new to stackoverflow – Bezique

Not related to the question, but never use lombok's Data or EqualsAndHashcode for an Entity. You can check the decompiled files: lombok does not understand entities. Entities are not value objects. Lombok's implementation of equals and hascode is both incorrect and a potential performance killer. Check e.g. @vlad-mihalcea blog vladmihalcea.com/… for a proper implementation. – Nitrate

Answer 1

You can define the column type using the @Column annotation:

@Column(columnDefinition="TIMESTAMP")  
@UpdateTimestamp
private ZonedDateTime updateDt;

Dirndl

This generates error. DDL is... create table note (id integer not null auto_increment, create_dt datetime(6), gsn varchar(255), schedule_dt TIMESTAMP, text varchar(255), type varchar(255), update_dt datetime(6), primary key (id)) engine=InnoDB – Donohue

Most likely the engine is different: your engine is InnoDB, however, in question defiinition MyISAM is used. – Binomial

@SimonMartinelli I added ddl above, because comment removes '`' – Donohue

Answer 2

JPA 2.2 offers support for mapping Java 8 Date/Time API, but only for the following types:

• java.time.LocalDate
• java.time.LocalTime
• java.time.LocalDateTime
• java.time.OffsetTime
• java.time.OffsetDateTime

However, Hibernate supports also ZonedDateTime, like this.

When saving the ZonedDateTime, the following Timestamp is going to be sent to the PreparedStatement:

Timestamp.from( zonedDateTime.toInstant() )

And, when reading it from the database, the ResultSet will contain a Timestamp that will be transformed to a ZonedDateTime, like this:

ts.toInstant().atZone( ZoneId.systemDefault() )

Note that the ZoneId is not stored in the database, so basically, you are probably better off using a LocalDateTime if this Timestamp conversion is not suitable for you.

So, let's assume we have the following entity:

@Entity(name = "UserAccount")
@Table(name = "user_account")
public class UserAccount {

    @Id
    private Long id;

    @Column(name = "first_name", length = 50)
    private String firstName;

    @Column(name = "last_name", length = 50)
    private String lastName;

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

    //Getters and setters omitted for brevity
}

Notice that the subscribedOn attribute is a ZonedDateTime Java object.

When persisting the UserAccount:

UserAccount user = new UserAccount()
    .setId(1L)
    .setFirstName("Vlad")
    .setLastName("Mihalcea")
    .setSubscribedOn(
        LocalDateTime.of(
            2020, 5, 1,
            12, 30, 0
        ).atZone(ZoneId.systemDefault())
    );

entityManager.persist(user);

Hibernate generates the proper SQL INSERT statement:

INSERT INTO user_account (
    first_name, 
    last_name, 
    subscribed_on, 
    id
) 
VALUES (
    'Vlad', 
    'Mihalcea', 
    '2020-05-01 12:30:00.0', 
    1
)

When fetching the UserAccount entity, we can see that the ZonedDateTime is properly fetched from the database:

UserAccount userAccount = entityManager.find(
    UserAccount.class, 1L
);

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

Upi

JDBC does not define support for ZonedDateTime, that is a non-standard extension, nor does JPA 2.2 define support for it (check your own blogpost and the JPA 2.2 changelog you link from that blogpost). – Lurleen

I updated the answer. Basically, Hibernate supports ZonedDateTime, and I explained how it does that even if JDBC does not offer native support for this type. – Upi

Thanks a lot for the explanation of how it works under the hood. Is this covered somewhere in Hibernate docs? There are also a number of questions relating to timestamps in Hibernate. How is Instant JPA field processed? How OffsetDateTime is mapped to a TIMESTAMP WITHOUT TIME ZONE column etc. – Cirrus