Storing LocalDateTime as DATETIME

In MYSQL the TIMESTAMP column type only supports dates until 2038 and so it makes sense to use the DATETIME type as this supports a much wider range of dates. By default it only stores seconds and no millis or micros.

The result is that times are rounded. 500 millis is rounded up to the next second. And this can give issues during the transition from wintertime to summertime. Imagine a time of 01:59:59.500 and store that in the MYSQL database. This will result in a time of 02:00:00. This time is invalid during the transition because the time then jumps from 01:59:59 to 03:00:00. Storing is fine but you won't be able to read it back. That time can't be represented by a millis since epoch value.

Using UTC for all backend dates and times obviously is a much better solution.

Solution is to make sure that you add a JPS converter that truncates the nanos before storing.

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.sql.Timestamp;
import java.time.LocalDateTime;

@Converter(autoApply = true)
public class LocalDateTimeJPAConverter implements AttributeConverter<LocalDateTime, Timestamp> {
   @Override
   public Timestamp convertToDatabaseColumn(LocalDateTime entityValue) {
      if(entityValue == null) {
         return null;
      }

      return Timestamp.valueOf(entityValue.withNano(0));
   }

   @Override
   public LocalDateTime convertToEntityAttribute(Timestamp databaseValue) {
      if (databaseValue == null) {
         return null;
      }

      return databaseValue.toLocalDateTime();
   }
}