currently I am facing a strange issue with Spring JPA : Whenever I try to map a date as Date only, it saves the day I want to save minus 1 day. E.g. saving 1st of Feb --> I have 31st of Jan in my DB. As long as I am using DateTime, everything is ok. Example : MySQL time zone set to System System is on UTC + 1 in connection URL, serverTimeZone is set to UTC and useLegacyDatetimeCode = true
@Entity
public class ZoneEntity
{
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
@CreationTimestamp
private LocalDateTime createDateTime;
@CreationTimestamp
private LocalDate createDate;
@Temporal(value = TemporalType.TIMESTAMP)
private Date temporalDate;
private LocalDateTime localDateTime;
private LocalDate localDate;
@Column(columnDefinition = "DATE")
private LocalDate localDateWithColumn;
public ZoneEntity() {
temporalDate = new Date();
localDateTime = LocalDateTime.now();
localDate = LocalDate.now();
localDateWithColumn = LocalDate.now();
}
I'll then save a new entity like this :
service.save(new ZoneEntity());
If I execute this today (24.02.2020), I'll end up with the following
createDateTime : 24.02.2020 15:20:00 --> Correct (UTC)
createDate : 23.02.2020 --> day - -1
temporalDate : 24.02.2020 15:20:00 --> Correct (UTC)
localDate : 23.02.2020 --> day - 1
localDateTime : 24.02.2020 15:20:00 --> Correct (UTC)
localDateWithColumn : 23.02.2020 --> day - 1
So however I save the date only, it ends with the wrong day. Does anybody have any idea why this happens ?
added:
If I have a look in the hibernate logging, the issue seems to be on the MySQL side...
Hibernate:
insert
into
zone_entity
(create_date, create_date_time, local_date, local_date_time, local_date_with_column, temporal_date, id)
values
(?, ?, ?, ?, ?, ?, ?)
2020-02-24 16:42:25.371 TRACE 19628 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [DATE] - [2020-02-24]
2020-02-24 16:42:25.374 TRACE 19628 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [TIMESTAMP] - [2020-02-24T16:42:25.365036900]
2020-02-24 16:42:25.375 TRACE 19628 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [DATE] - [2020-02-24]
2020-02-24 16:42:25.375 TRACE 19628 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [TIMESTAMP] - [2020-02-24T16:42:25.302181]
2020-02-24 16:42:25.375 TRACE 19628 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [5] as [DATE] - [2020-02-24]
2020-02-24 16:42:25.376 TRACE 19628 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [6] as [TIMESTAMP] - [Mon Feb 24 16:42:25 CET 2020]
2020-02-24 16:42:25.376 TRACE 19628 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [7] as [BIGINT] - [294]
added : if I try to insert via MySQL workbench with the values below (as the parameters from Hibernate look like), everything is ok (left out the timestamp to make it a bit shorter).
insert
into
zone_entity
(create_date, create_date_time, local_date, local_date_time, local_date_with_column,id)
values
('2020-02-24', '2020-02-24T22:03:13.842324', '2020-02-24', '2020-02-24T22:03:13.842324', '2020-02-24',99);
added: I now changed time zone in DB URL to Europe/London --> everything works as it should. Changing to UTC or Europe/London --> both does not work !
spring.datasource.url = jdbc:mysql://localhost:3306/myDatabase?serverTimezone=Europe/Berlin&useLegacyDatetimeCode=false
Many thanks in advance !
Kind regards
Joern
SHOW VARIABLES LIKE '%zone%';
Also, I don't think MySQL likes the "T" in the middle. – Rick James