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
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 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")
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
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
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
@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());
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 dateBUTC = new MyDateBusinessToUTC(entity.getCreatedDate(), ZoneId.of("Europe/Lisbon"));
entity.setMarketDate(dateBUTC.getMarketDate());
entity.setDatetimeKeyUTC(dateBUTC.getDatetimeKeyUtcHour());
return entity;
}
}
The return
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.
if ( java.sql.Timestamp.class.isAssignableFrom( type ) ) {
ZonedDateTime zonedDateTime = value.atZone(zoneUTC);
return (X) Timestamp.from(zonedDateTime.toInstant());
}
|
