12:08 p.m.
humbertosales NA (
https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%...
) *created* an issue
Hibernate ORM (
https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZDk0OGY0MjUz...
) / Bug (
https://hibernate.atlassian.net/browse/HHH-16722?atlOrigin=eyJpIjoiZDk0OG...
) HHH-16722 (
https://hibernate.atlassian.net/browse/HHH-16722?atlOrigin=eyJpIjoiZDk0OG...
) LocalDateTime values are wrong in START and END Daylight saving (non UTC timezone) (
https://hibernate.atlassian.net/browse/HHH-16722?atlOrigin=eyJpIjoiZDk0OG...
)
Issue Type: Bug Affects Versions: 5.6.15 Assignee: Unassigned Components: hibernate-core
Created: 30/May/2023 09:08 AM Environment: hibernate-core 5.6.15.Final
quarkus 2.16.6.FINAL
openjdk 17.0.7 2023-04-18
Windows 10 64 bits
SQL Server 2019 or H2 Memory DB (both)
Local timezone is WET Priority: Major Reporter: humbertosales NA (
https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%...
)
***********
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-localdatet...
@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());
}
(
https://hibernate.atlassian.net/browse/HHH-16722#add-comment?atlOrigin=ey...
) Add Comment (
https://hibernate.atlassian.net/browse/HHH-16722#add-comment?atlOrigin=ey...
)
Get Jira notifications on your phone! Download the Jira Cloud app for Android (
https://play.google.com/store/apps/details?id=com.atlassian.android.jira....
) or iOS (
https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em...
) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100225- sha1:d82e018 )