LocalDateTime values are wrong in START and END Daylight saving (non UTC timezone)
Description
Activity
Show:
humbertosales NA May 30, 2023 at 6:27 PM
Workaround with Converters
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
@Converter(autoApply = true)
public class LocalDateTimeToTimestampConverter implements AttributeConverter<LocalDateTime, Timestamp> {
@Override
public Timestamp convertToDatabaseColumn(LocalDateTime attribute) {
ZonedDateTime zonedDateTime = attribute.atZone(ZoneId.of("UTC"));
return Timestamp.from(zonedDateTime.toInstant());
}
@Override
public LocalDateTime convertToEntityAttribute(Timestamp dbData) {
return dbData.toInstant().atZone(ZoneId.of("UTC")).toLocalDateTime();
}
}
Details
Details
Assignee
Unassigned
UnassignedReporter
humbertosales NA
humbertosales NAComponents
Affects versions
Priority
Created May 30, 2023 at 4:08 PM
Updated May 30, 2023 at 6:27 PM
The problem
The date 2023-03-26 is DAYLIGTH SAVING START in WET (Europe/Lisbon). The 01:00 at 2023-03-26 in timezone WET not exists.
When persist this date in java.time.LocalDateTime (withou timezone) the hibernate convert it to java.sql.Timestamp (with timezone) before persist and lost the time because local timezone (JVM) is Europe/Lisbon. The JVM by convetion shift time to upper when it not exists in date time local (OS timezone).
When get it data from DB, the Hibernate try re-converter timestamp to LocaDateTime, but the time is wrong and re-converter is impossible.
Atention: the timezone JDBC properties not work here. It is used after the lost time describe.
UTC
WET
Description
2023-03-26T00:00
2023-03-26T00:00
No problem
2023-03-26T01:00
2023-03-26T02:00
Problem! The time was converted to 02:00. Not is possible determined the original time is 01:00 or 02:00.
2023-03-26T02:00
2023-03-26T02:00
Problem. It was 01:00?!
2023-03-26T03:00
2023-03-26T03:00
No problem.
The hibernate code
//org.hibernate.type.descriptor.java.LocalDateTimeJavaDescriptor public class LocalDateTimeJavaDescriptor extends AbstractTypeDescriptor<LocalDateTime> { //... public <X> X unwrap(LocalDateTime value, Class<X> type, WrapperOptions options) { if ( value == null ) { return null; } if ( LocalDateTime.class.isAssignableFrom( type ) ) { return (X) value; } if ( java.sql.Timestamp.class.isAssignableFrom( type ) ) { /* * Workaround for HHH-13266 (JDK-8061577). * We used to do Timestamp.from( value.atZone( ZoneId.systemDefault() ).toInstant() ), * but on top of being more complex than the line below, it won't always work. * Timestamp.from() assumes the number of milliseconds since the epoch * means the same thing in Timestamp and Instant, but it doesn't, in particular before 1900. */ return (X) Timestamp.valueOf( value ); //THE BIG PROBLEM <<<<<<<<<< } //... }
The Evidence
Entity
import java.time.LocalDate; import java.time.LocalDateTime; import java.util.UUID; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; import org.hibernate.annotations.GenericGenerator; import lombok.Data; @Entity @Table(name = "DT_ENTITY_WITH_DATETIME") @Data public class EntityWithDatetime { private static final long serialVersionUID = 1L; @Id @GeneratedValue(generator = "UUID") @GenericGenerator(name = "UUID", strategy="org.hibernate.id.UUIDGenerator") @Column(name="ID") private UUID id; @Column(name = "CREATED_DATE") // @Column(name = "CREATED_DATE", columnDefinition = "DATETIME2") same error // @Column(name = "CREATED_DATE", columnDefinition = "DATETIME") same error private LocalDateTime createdDate; @Column(name = "DATETIME_KEY_UTC") private Long datetimeKeyUTC; @Column(name = "MARKET_DATE") private LocalDate marketDate; @Column(name = "MARKET_HOUR") private Integer marketHour; }
Repository
import javax.enterprise.context.ApplicationScoped; @ApplicationScoped public class EntityWithDatetimeRepository implements PanacheRepository<EntityWithDatetime> { }
Unit tests
import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertNotEquals; import java.sql.Timestamp; import java.time.LocalDateTime; import java.time.ZoneId; import java.time.ZonedDateTime; import org.junit.jupiter.api.Test; import org.junit.jupiter.api.extension.ExtendWith; import org.mockito.junit.jupiter.MockitoExtension; @ExtendWith(MockitoExtension.class) public class LocaldatetimeTimestampTest { //Test issue in org.hibernate.type.descriptor.java.LocalDateTimeJavaDescriptor @Test public void testLocalDateTimeTOTimeStamp() { LocalDateTime localdatetime = LocalDateTime.parse("2023-03-26T01:00"); Timestamp timestamp = Timestamp.valueOf(localdatetime); assertNotEquals("2023-03-26 01:00:00.0", timestamp.toString()); } //Test issue in org.hibernate.type.descriptor.java.LocalDateTimeJavaDescriptor //work? https://stackoverflow.com/questions/72909759/adding-minutes-to-localdatetime-gives-error-on-daylight-saving-days @Test public void testLocalDateTimeTOTimeStampTry1() { LocalDateTime localdatetime = LocalDateTime.parse("2023-03-26T01:00"); ZonedDateTime zonedDateTime = localdatetime.atZone(ZoneId.of("UTC")); Timestamp timestamp = Timestamp.from(zonedDateTime.toInstant()); LocalDateTime localDateTimeReturned = timestamp.toInstant().atZone(ZoneId.of("UTC")).toLocalDateTime(); assertEquals("2023-03-26T01:00", localDateTimeReturned.toString()); } }
Integration Test
import static org.junit.jupiter.api.Assertions.assertEquals; import java.io.IOException; import java.sql.Timestamp; import java.time.LocalDateTime; import java.time.ZoneId; import java.util.Arrays; import java.util.List; import java.util.TimeZone; import javax.inject.Inject; import javax.persistence.EntityManager; import javax.persistence.Tuple; import javax.transaction.Transactional; import org.junit.jupiter.api.Test; import io.quarkus.panache.common.Sort; import io.quarkus.test.common.QuarkusTestResource; import io.quarkus.test.junit.QuarkusTest; @QuarkusTest public class DateTimeIntegrationTest { private static final String QuerySelectMarketDateAndHour = "Select CREATED_DATE, MARKET_HOUR, DATETIME_KEY_UTC from DT_ENTITY_WITH_DATETIME A " + "where MARKET_HOUR in (0, 1, 2, 3, 22, 23, 24) order by market_hour"; @Inject EntityWithDatetimeRepository repository; @Inject EntityManager em; @Test //@DataSet(value = "datasets/FunctionalTest.yaml", strategy = SeedStrategy.IDENTITY_INSERT, cleanBefore = true, fillIdentityColumns = true) @Transactional public void test() throws IOException { EntityWithDatetime entity00 = createEntityWithDateTime("2023-03-26T00:00"); EntityWithDatetime entity01 = createEntityWithDateTime("2023-03-26T01:00"); EntityWithDatetime entity02 = createEntityWithDateTime("2023-03-26T02:00"); EntityWithDatetime entity03 = createEntityWithDateTime("2023-03-26T03:00"); repository.persist(Arrays.asList(entity00, entity01, entity02, entity03)); assertEquals(4, repository.findAll().count()); //lastversion List<Tuple> resultList = em.createNativeQuery(QuerySelectMarketDateAndHour, Tuple.class).getResultList(); System.out.println(QuerySelectMarketDateAndHour); for (Tuple tuple : resultList) { Object[] cols = tuple.toArray(); for (Object col : cols) { if (col instanceof Timestamp) { System.out.print(String.format("%s (%s) (%s %s) \t", col, ((Timestamp) col).toGMTString(), ((Timestamp) col).toLocaleString(), TimeZone.getDefault().toZoneId().toString())); }else { System.out.print(String.format("%s \t", col)); } } System.out.println(""); } assertEquals("2023-03-26T00:00", ((Timestamp)resultList.get(0).get("CREATED_DATE")).toInstant().atZone(ZoneId.of("UTC")).toLocalDateTime().toString()); assertEquals("2023-03-26T01:00", ((Timestamp)resultList.get(1).get("CREATED_DATE")).toInstant().atZone(ZoneId.of("UTC")).toLocalDateTime().toString()); assertEquals("2023-03-26T02:00", ((Timestamp)resultList.get(2).get("CREATED_DATE")).toInstant().atZone(ZoneId.of("UTC")).toLocalDateTime().toString()); assertEquals("2023-03-26T03:00", ((Timestamp)resultList.get(3).get("CREATED_DATE")).toInstant().atZone(ZoneId.of("UTC")).toLocalDateTime().toString()); System.out.println("ENTITIES PRINTS"); List<EntityWithDatetime> listAll = repository.findAll(Sort.by("marketHour")).list(); for (EntityWithDatetime entity : listAll) { System.out.println(entity); } assertEquals("2023-03-26T00:00", listAll.get(0).getCreatedDate().toString()); assertEquals("2023-03-26T01:00", listAll.get(1).getCreatedDate().toString()); assertEquals("2023-03-26T02:00", listAll.get(2).getCreatedDate().toString()); assertEquals("2023-03-26T03:00", listAll.get(3).getCreatedDate().toString()); } private EntityWithDatetime createEntityWithDateTime(String datetimeCET) { EntityWithDatetime entity = new EntityWithDatetime(); entity.setCreatedDate(LocalDateTime.parse(datetimeCET)); entity.setMarketHour(entity.getCreatedDate().getHour()); //MyDateBusinessToUTC calculate it in UTC.... irrelevant business code!! MyDateBusinessToUTC dateBUTC = new MyDateBusinessToUTC(entity.getCreatedDate(), ZoneId.of("Europe/Lisbon")); entity.setMarketDate(dateBUTC.getMarketDate()); entity.setDatetimeKeyUTC(dateBUTC.getDatetimeKeyUtcHour()); return entity; } }
The return
Hibernate: insert into ODS.DT_ENTITY_WITH_DATETIME (CREATED_DATE, DATETIME_KEY_UTC, MARKET_DATE, MARKET_HOUR, ID) values (?, ?, ?, ?, ?) 2023-05-30 17:00:55,839 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [1] as [TIMESTAMP] - [2023-03-26T00:00] 2023-05-30 17:00:55,845 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [2] as [BIGINT] - [2023032600] 2023-05-30 17:00:55,846 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [3] as [DATE] - [2023-03-26] 2023-05-30 17:00:55,846 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [4] as [INTEGER] - [0] 2023-05-30 17:00:55,847 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [5] as [BINARY] - [1d29f0d8-7f5a-4b58-b6c9-c22d0b4a1d61] Hibernate: insert into ODS.DT_ENTITY_WITH_DATETIME (CREATED_DATE, DATETIME_KEY_UTC, MARKET_DATE, MARKET_HOUR, ID) values (?, ?, ?, ?, ?) 2023-05-30 17:00:55,849 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [1] as [TIMESTAMP] - [2023-03-26T01:00] 2023-05-30 17:00:55,850 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [2] as [BIGINT] - [2023032601] 2023-05-30 17:00:55,850 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [3] as [DATE] - [2023-03-26] 2023-05-30 17:00:55,850 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [4] as [INTEGER] - [1] 2023-05-30 17:00:55,850 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [5] as [BINARY] - [e6084c0c-efbe-4ffa-a5f7-76e9ddfd1d48] Hibernate: insert into ODS.DT_ENTITY_WITH_DATETIME (CREATED_DATE, DATETIME_KEY_UTC, MARKET_DATE, MARKET_HOUR, ID) values (?, ?, ?, ?, ?) 2023-05-30 17:00:55,851 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [1] as [TIMESTAMP] - [2023-03-26T02:00] 2023-05-30 17:00:55,851 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [2] as [BIGINT] - [2023032601] 2023-05-30 17:00:55,852 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [3] as [DATE] - [2023-03-26] 2023-05-30 17:00:55,852 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [4] as [INTEGER] - [2] 2023-05-30 17:00:55,852 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [5] as [BINARY] - [b3318092-94e4-4d83-bc16-c0ec65c95e14] Hibernate: insert into ODS.DT_ENTITY_WITH_DATETIME (CREATED_DATE, DATETIME_KEY_UTC, MARKET_DATE, MARKET_HOUR, ID) values (?, ?, ?, ?, ?) 2023-05-30 17:00:55,853 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [1] as [TIMESTAMP] - [2023-03-26T03:00] 2023-05-30 17:00:55,853 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [2] as [BIGINT] - [2023032602] 2023-05-30 17:00:55,853 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [3] as [DATE] - [2023-03-26] 2023-05-30 17:00:55,854 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [4] as [INTEGER] - [3] 2023-05-30 17:00:55,854 TRACE [org.hib.typ.des.sql.BasicBinder] (main) binding parameter [5] as [BINARY] - [b2b0dc24-0f5e-403d-8d45-a806f4e33b7f] Hibernate: select count(*) as col_0_0_ from DT_ENTITY_WITH_DATETIME entitywith0_ Hibernate: Select CREATED_DATE, MARKET_HOUR, DATETIME_KEY_UTC from ODS.DT_ENTITY_WITH_DATETIME A where MARKET_HOUR in ( 0, 1, 2, 3, 22, 23, 24 ) order by market_hour Select CREATED_DATE, MARKET_HOUR, DATETIME_KEY_UTC from ODS.DT_ENTITY_WITH_DATETIME A where MARKET_HOUR in (0, 1, 2, 3, 22, 23, 24) order by market_hour 2023-03-26 00:00:00.0 (26 Mar 2023 00:00:00 GMT) (26 de mar de 2023 00:00:00 Europe/London) 0 2023032600 2023-03-26 02:00:00.0 (26 Mar 2023 01:00:00 GMT) (26 de mar de 2023 02:00:00 Europe/London) 1 2023032601 2023-03-26 02:00:00.0 (26 Mar 2023 01:00:00 GMT) (26 de mar de 2023 02:00:00 Europe/London) 2 2023032601 2023-03-26 03:00:00.0 (26 Mar 2023 02:00:00 GMT) (26 de mar de 2023 03:00:00 Europe/London) 3 2023032602
The Reference:
See org.hibernate.type.descriptor.java.LocalDateTimeJavaDescriptor.
See https://savvytime.com/converter/utc-to-wet/mar-26-2023/1-15am
Workaround:
Create Types overwrites, forcing ALWAYS timezone UTC.
//org.hibernate.type.descriptor.java.LocalDateTimeJavaDescriptor if ( java.sql.Timestamp.class.isAssignableFrom( type ) ) { ZonedDateTime zonedDateTime = value.atZone(zoneUTC); return (X) Timestamp.from(zonedDateTime.toInstant()); }