How to map PostgreSQL 'infinity' value to LocalDateTime in Spring Data R2DBC?

Question

Java field,

private LocalDateTime expireOn;

In the database, it is auto-generated when there is no value or a null value passed from API.

`LocalDateTime` is the **wrong class** to use in recording a moment such as creation-date . That lacks the context of a time zone or offset-from-UTC. So it cannot represent a moment, a specific point on the timeline. In Java, represent a moment using `Instant`, `OffsetDateTime`, or `ZonedDatetime`. Search Stack Overflow to learn more. This issue has been addressed many times already. — Basil Bourque, Feb 16 '23 at 06:38
If the column is an audit field containing creation-date, and is auto-generated, why would it ever contain a value of `infinity`? — Basil Bourque, Feb 16 '23 at 06:40

score 0 · Answer 1 · answered Feb 16 '23 at 09:33

You need to create a converter. Sample converter from my project:

import ru.pochta.abon.library.util.DateUtils;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.time.ZonedDateTime;
import java.util.Calendar;
import java.util.TimeZone;

@Converter(autoApply = true)
public class ZonedDateTimeConverter implements     AttributeConverter<ZonedDateTime, Calendar> {
    @Override
    public Calendar convertToDatabaseColumn(ZonedDateTime entityAttribute) {
        if (entityAttribute == null) {
            return null;
        }
        return DateUtils.convertZDTtoCalendar(entityAttribute);
    }

    @Override
    public ZonedDateTime convertToEntityAttribute(Calendar databaseColumn) {
        if (databaseColumn == null) {
        return null;
        }

        return ZonedDateTime.ofInstant(databaseColumn.toInstant(),
            databaseColumn
                    .getTimeZone()
                    .toZoneId());
    }
}
...
(method from the class DateUtils)
public static Calendar convertZDTtoCalendar(ZonedDateTime zdt) {
    Calendar calendar = Calendar.getInstance();
    calendar.setTimeInMillis(zdt.toInstant().toEpochMilli());
    calendar.setTimeZone(TimeZone.getTimeZone(zdt.getZone()));
    return calendar;
}

Usage:

@Column(name = "create_date")
@Convert(converter = ZonedDateTimeConverter.class)
private ZonedDateTime createDate;

The value 'infinity' will require special handling. If it's a string, your converter will implement AttributeConverter<ZonedDateTime, String> accordingly. Converter handles conversion in the both directions.

How to map PostgreSQL 'infinity' value to LocalDateTime in Spring Data R2DBC?

1 Answers1