LocalDateTime values are wrong in START and END Daylight saving (non UTC timezone)

Description

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()); }

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

Assignee

Reporter

Components

Affects versions

Priority

Created May 30, 2023 at 4:08 PM
Updated May 30, 2023 at 6:27 PM