10

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.

Improve this question
5
  • 2
    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. Commented Jun 18, 2019 at 11:32
  • @JB Yes, you're right. Timezone is not recoverable. Anyway Instant, LocalDateTime do not work, too. Generated DDL uses DATETIME colume type. Commented Jun 18, 2019 at 11:37
  • @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. Commented Jun 18, 2019 at 19:04
  • ok thanks for the advice, im new to stackoverflow Commented Jun 18, 2019 at 20:08
  • 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. Commented Dec 15, 2021 at 7:54

2 Answers 2

Reset to default
19

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

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() );
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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).
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.
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.
3

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

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

4 Comments

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
Most likely the engine is different: your engine is InnoDB, however, in question defiinition MyISAM is used.
@SimonMartinelli I added ddl above, because comment removes '`'
This solution probably works while saving your objects to the database but you loose the time zone information embedded in the ZonedDateTime class. ZonedDateTime contains both a timestamp plus a zone id. You probably need a separate column with the zoneId if you have different users saving objects from different time zones and if it is important that the zone id is not lost when fetching the information again. If it is not important to save the zone id, you should probably change the datatype to Instant in your domain class instead.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.