LocalTime.MAX and Postgres

Hibernate ORM
1

Hello,
I’m using Spring Boot 3.2 with Hibernate 6.3.1.Final and I have the following entity

@Entity
@Getter
@Setter
public class DummyEntity {

    @Id
    private Long id;
    private String name;
    private LocalTime myTime;
}

Seems some values are not saved correctly. Here some examples

If I try to save LocalTime.MAX then the saved value will be “00:00”
If I try to save LocalTime.MAX.withNano(999_999_000) then the saved value will be “00:00”
If I try to save LocalTime.MAX.withNano(999_000_000) then the saved value is right.

For better understand I have created a project on github, and the original question is there

Are my expectations wrong or am I doing another error?

2

The problem is that not every database can store an arbitrary amount of fractional seconds, so the database will round a value up. I don’t think Hibernate can reasonably do anything about this since it’s the JDBC driver or the database itself that takes a value and does the rounding.

3

My doubt regards the LocalTimeJavaType::unwrap method.

If the input is LocalTime.MAX, then the returned java.sql.Time is “00:00”.
That means that if I try to save LocalTime.MAX, the saved value will be LocalTime.MIN, and the following test will fail.

    @Test
    void shouldSaveLocalTimeMaxCorrectly() {
        var entity = new DummyEntity();
        entity.setLocalTime(LocalTime.MAX);

        dummyRepository.save(entity);

        assertThat(dummyRepository.findByLocalTime(LocalTime.MIN)).isEmpty();
    }

I don’t know if it is the expected behaviour

4

LocalTime.MIN and LocalTime.MAX are values that simply don’t work well for multiple reasons:

  • java.sql.Time has millisecond resolution, so any conversion will have to do rounding
  • Most databases don’t support nanosecond resolution for their time data types, so some rounding will happen even when passing a LocalTime value to the JDBC driver directly or via literal. Try your favorite database and see what happens when you run select time '12:00:00.999999999'. It will round up to 12:00:01 if it doesn’t support nanoseconds.

It’s IMO questionable to use fractional seconds anyway for time values, so I would suggest you just set the fractions to 0.

5

Clear. For the purpose we need I have created a custom converter. I attach here, may can be useful for someone

import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;
import org.hibernate.type.descriptor.DateTimeUtils;

import java.sql.Time;
import java.time.LocalTime;
import java.time.temporal.ChronoField;

@Converter(autoApply = true)
public class LocalTimeCustomConverter implements AttributeConverter<LocalTime, Time> {
    @Override
    public Time convertToDatabaseColumn(LocalTime value) {
        Time res = null;

        if (value != null) {
            res = Time.valueOf(value);
            if (value.getNano() > 0) {
                long millis = DateTimeUtils.roundToPrecision(value.getNano(), 3) / 1_000_000;

                if (millis >= 1_000) {
                    millis = 999;
                }

                res = new Time(res.getTime() + millis);
            }
        }

        return res;
    }

    @Override
    public LocalTime convertToEntityAttribute(Time dbData) {
        LocalTime res = null;

        if (dbData != null) {
            LocalTime localTime = dbData.toLocalTime();
            final long millis = dbData.getTime() % 1_000;
            if (millis == 0) {
                res = localTime;
            } else {
                res = localTime.with(ChronoField.NANO_OF_SECOND, millis * 1_000_000L);
            }
        }

        return res;
    }
}

Thank you