Unable to find records by LocalDate on dates with clock changes occurring at midnight

[acmi]

When using the java.time.LocalDate type as a parameter for querying (which is converted to java.sql.Timestamp during the execution), some databases return an empty result in certain cases. For instance, this occurs with dates where a clock change happens at midnight.

Code snippet to reproduce (Daylight Saving Time began in Egypt on April 26, 2024):

import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.ValueSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.data.annotation.Id;
import org.springframework.data.jdbc.core.JdbcAggregateOperations;
import org.springframework.data.relational.core.mapping.Table;
import org.testcontainers.containers.JdbcDatabaseContainer;
import org.testcontainers.containers.MSSQLServerContainer;
import org.testcontainers.containers.MariaDBContainer;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.ext.ScriptUtils;
import org.testcontainers.jdbc.JdbcDatabaseDelegate;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.time.LocalDate;
import java.util.TimeZone;

import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.springframework.data.relational.core.query.Criteria.where;
import static org.springframework.data.relational.core.query.Query.query;

@SpringBootTest(properties = {
        "logging.level.org.springframework.jdbc.core=trace",
})
@Testcontainers
public class LocalDateTest {
    @Container
    @ServiceConnection
    static JdbcDatabaseContainer<?> db = new MariaDBContainer<>();        // AFFECTED
//    static JdbcDatabaseContainer<?> db = new MSSQLServerContainer<>();  // AFFECTED
//    static JdbcDatabaseContainer<?> db = new PostgreSQLContainer<>();   // NOT AFFECTED

    @Autowired
    JdbcAggregateOperations template;

    @BeforeAll
    static void setUp() throws Exception {
        TimeZone.setDefault(TimeZone.getTimeZone("Egypt"));

        ScriptUtils.executeDatabaseScript(
                new JdbcDatabaseDelegate(db, ""),
                null,
                "create table with_local_date (id int primary key, test_date date)"
        );
    }

    @ParameterizedTest
    @ValueSource(strings = {
            "2024-04-25", // OK   | 2024-04-24T23:59:59+02:00[Egypt] → 2024-04-25T00:00:00+02:00[Egypt]
            "2024-04-26", // FAIL | 2024-04-25T23:59:59+02:00[Egypt] → 2024-04-26T01:00:00+03:00[Egypt]
            "2024-04-27", // OK   | 2024-04-26T23:59:59+03:00[Egypt] → 2024-04-27T00:00:00+03:00[Egypt]
    })
    void saveAndFindByLocalDate(LocalDate testDate) {
        var entity = new WithLocalDate();
        entity.id = testDate.hashCode();
        entity.testDate = testDate;

        template.insert(entity);

        var loaded = template.findOne(query(where("testDate").is(testDate)), WithLocalDate.class);

        assertTrue(loaded.isPresent());
    }

    @Table
    static class WithLocalDate {
        @Id
        Integer id;
        LocalDate testDate;
    }
}

[mp911de]

The root cause is a mismatch between data types. We adopt LocalDate into java.sql.Timestamp that is always associated with a time component, not only a year/month/date.

Have you tried implementing your own variant of LocalDateToTimestampConverter to control how LocalDate gets converted into Timestamp?

[acmi]

It seems that converting java.time.LocalDate to java.sql.Timestamp is not entirely accurate and might even be impossible in some cases. I suppose this issue arises because there is no 00:00 local time on such dates. For dates, there is a specific type: java.sql.Date. As a solution, we registered a mapping from java.time.LocalDate to java.sql.Date in org.springframework.data.jdbc.core.convert.JdbcColumnTypes class via reflection. I'm not sure if this solution is applicable for the entire Spring Data JDBC framework. This is an extremely rare case for a specific time zone, and we discovered it completely by accident. While it might seem minor within a single time zone, it actually affects a quite extensive list of time zones and dates. You can view the approximate list of affected dates with the following code snippet:

var lowBound = LocalDate.parse("1930-01-01");
for (var zoneId : TimeZone.getAvailableIDs()) {
    ZoneId zone;
    try {
        zone = ZoneId.of(zoneId);
    } catch (ZoneRulesException e) {
        continue;
    }
    var affectedDates = new ArrayList<LocalDate>();
    for (var date = LocalDate.now(); !date.isBefore(lowBound); date = date.minusDays(1)) {
        if (date.atStartOfDay(zone).toLocalTime().isAfter(LocalTime.MIDNIGHT)) {
            affectedDates.add(date);
        }
    }
    if (!affectedDates.isEmpty()) {
        System.out.println(zoneId + ": " + affectedDates);
    }
}

Without fixing this case, using the java.time.LocalDate type seems very unreliable.