LocalDateTime , ZonedDateTime and Timestamp

Question

I have a SpringBoot app. using Spring Initializer, embedded Tomcat, Thymeleaf template engine, and package as an executable JAR file.

I have a domain object with 2 properties (initDate, endDate). I want to create 2 converters to deal with mySQL DB

@Convert(converter = LocalDateTimeAttributeConverter.class) 
private LocalDateTime initDate;

@Convert(converter = ZonedDateTimeAttributeConverter.class) 
private ZonedDateTime endDate;

the converter 1 (is OK)

@Converter
public class LocalDateTimeAttributeConverter implements AttributeConverter<LocalDateTime, Timestamp> {

    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime localDateTime) {
        return (localDateTime == null ? null : Timestamp.valueOf(localDateTime));
    }

    @Override
    public LocalDateTime convertToEntityAttribute(Timestamp sqlTimestamp) {
        return (sqlTimestamp == null ? null : sqlTimestamp.toLocalDateTime());
    }
}

This is the one that I want to create

@Converter
public class ZonedDateTimeAttributeConverter implements AttributeConverter<ZonedDateTime, Timestamp> {

    @Override
    public Timestamp convertToDatabaseColumn(ZonedDateTime zoneDateTime) {
        return (zoneDateTime == null ? null : Timestamp.valueOf(zoneDateTime));
    }


    @Override
    public ZonedDateTime convertToEntityAttribute(Timestamp sqlTimestamp) {
        return (sqlTimestamp == null ? null : sqlTimestamp.toZonedDateTime());
    }
}

But I can't because I have 2 errors:

The method valueOf(String) in the type Timestamp is not applicable for the arguments (ZonedDateTime)

and the TimeStamp does not have the method toZonedDateTime()

and if I don't add any converter for the ZonedDate, JPA creates a table with the type varbinary(255)

Wert

Well which time zone do you want it in? (It seems pretty broken than you can convert a Timestamp to a LocalDateTime, but there we go.) You may well want to go via Timestamp.toInstant() and Timestamp.from(Instant). – Uniformed

Answer 1

Timestamp extends Date to provide nanosecond accuracy. Neither Date nor Timestamp are designed to refer to a specific timezone as ZoneDateTime.

If you need to convert ZonedDateTime -> Timestamp you will have to discard the timezone/offset information. E.g.

LocalDateTime withoutTimezone = zoneDateTime.toLocalDateTime();
Timestamp timestamp = Timestamp.valueOf(withoutTimezone));

and for converting Timestamp -> ZonedDateTime you need to specify an offset:

LocalDateTime withoutTimezone = sqlTimestamp.toLocalDateTime();
ZonedDateTime withTimezone = withoutTimezone.atZone(ZoneId.of("+03:00"));

or timezone:

ZonedDateTime withTimezone = withoutTimezone.atZone(ZoneId.of("Europe/Paris"));

If your intention is to save ZonedDateTime variables in the database and preserve the various timezones specified there, I recommend designing your database accordingly. Suggestions:

1. Use a column of type DATETIME to save a LocalDateTime and a VARCHAR saving a timezone like "Europe/Paris" or a SMALLINT saving an offset in minutes.
2. Convert the ZonedDateTime to a String and save in a VARCHAR column like "2017-05-16T14:12:48.983682+01:00[Europe/London]". You'll then have to parse it when reading from the database.

Quoth

Upvoted for the obviously good suggestion of designing the database to hold the time zone information too. – Guzzle

withoutTimezone.atZone(ZoneId.of("Europe/Paris")) is same with sqlTimestamp.toInstant().atZone(ZoneId.of("Europe/Paris")), it just simply added timezone. – Lim

Answer 2

Jon Skeet said it already:

@Override
public Timestamp convertToDatabaseColumn(ZonedDateTime zoneDateTime) {
    return zoneDateTime == null ? null : Timestamp.from(zoneDateTime.toInstant());
}

@Override
public ZonedDateTime convertToEntityAttribute(Timestamp sqlTimestamp) {
    return sqlTimestamp == null ? null : sqlTimestamp.toInstant().atZone(ZoneId.systemDefault());
}

Jon also asked the good question, which time zone do you want? I have guessed at ZoneId.systemDefault(). Obviously a different time zone will give a different result, so I hope you will think twice and will be able to find the right time zone for your purpose.

PS I have reduced the usage of parentheses since I found it more readable with fewer. You can add them back in if you prefer.

Guzzle