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

Unassigned

Reporter

humbertosales NA

Components

hibernate-core

Affects versions

5.6.15

Priority

Major

Created May 30, 2023 at 4:08 PM

Updated May 30, 2023 at 6:27 PM

Configure