14

When saving a LocalDate field (e.g. '2017-09-27') to a mySQL Date column using JPA CriteriaBuilder API, the result is different (e.g. '2017-09-26').

I have validated that my database's timezone is set to UTC using SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as the result is '00:00:00'.

I am testing this locally, and I have a timezone of GMT + 2, so my suspicion is that when the conversion occurs from LocalDate to Date, 2 hours are being deducted and producing a date 1 day before the requested date (assuming that the LocalDate field, as a result of it having no time information, is being treated as 00:00:00.

What is the best way to save LocalDates in this situation? Should I be following the advice here https://stackoverflow.com/a/29751575/3832047 and explicitly setting all LocalDate fields to UTC or something similar?

I ran a test to see what happens when converting them in code and got the following result:

Date convertedDate = Date.valueOf(localDate);

conversion-result

EDIT

Here is an example of the code I use to retrieve the data, where the odd date change occurs as well. If I request data for 2017-06-27, I receive results for 2017-06-26. 

CriteriaBuilder criteriaBuilder = sessionFactory.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(HorseAndTrailerRequest.class);
Root<HorseAndTrailerRequest> criteria = criteriaQuery.from(HorseAndTrailerRequest.class);

ParameterExpression<LocalDate> effectiveDateParameter = criteriaBuilder.parameter(LocalDate.class);
    criteriaQuery.select(criteria)
            .where(
                    criteriaBuilder.equal(criteria.get("effectiveDate"), effectiveDateParameter)
            );

TypedQuery<HorseAndTrailerRequest> query = sessionFactory.getCurrentSession().createQuery(criteriaQuery);
query.setParameter(effectiveDateParameter, date);
return query.getResultList();
Phobos
  • 1,568
  • 10
  • 18
  • Java `LocalDate` objects store no time or timezone information (see [Javadoc](https://docs.oracle.com/javase/8/docs/api/java/time/LocalDate.html)], I suspect something else. – Tim Biegeleisen Sep 27 '17 at 11:27
  • Can you try running the following to confirm your MySQL server timezone: `SELECT @@system_time_zone, NOW(), UTC_TIMESTAMP()` – Tim Biegeleisen Sep 27 '17 at 11:28
  • @TimBiegeleisen The result is `UTC | 2017-09-27 11:28:51 | 2017-09-27 11:28:51`. – Phobos Sep 27 '17 at 11:29
  • I upvoted your question, but at this point I think we need to see the Java code which is saving the local dates into your MySQL. – Tim Biegeleisen Sep 27 '17 at 11:33
  • Ok thanks, it's occurring on reads as well - when doing a query requesting data from `2017-09-27' I'm receiving data from '2017-09-26' only, very odd behaviour. I'll edit the question with an example read. – Phobos Sep 27 '17 at 11:39

2 Answers2

2

Since LocalDate has no TimeZone, you can map the column_date as long in your database schema, and use AttributeConverter to convert LocalDate to long to avoid time zone conversion problems :

import javax.persistence.Converter;
import java.time.LocalDate;
import javax.persistence.AttributeConverter;
@Converter
public class LocalDateToLong implements AttributeConverter<LocalDate, Long> {

    @Override
    public Long convertToDatabaseColumn(LocalDate date) {
        if (date != null) {
            long epochDay = date.toEpochDay();
            return epochDay;
        }
        return null;
    }

    @Override
    public LocalDate convertToEntityAttribute(Long epochDay) {
        // TODO Auto-generated method stub
        if (epochDay != null) {
            LocalDate date = LocalDate.ofEpochDay(epochDay);
            return date;
        }
        return null;
    }

}
SEY_91
  • 1,615
  • 15
  • 26
1

I had the same issue and I solved it seeing the hibernate atlassian issues ( here is the thread: https://hibernate.atlassian.net/browse/HHH-11396) . You have two alternatives:

  1. Setting the JVM using -Duser.timezone=Europe/Berlin or programmatically by TimeZone.setDefault(TimeZone.getTimeZone("Europe/Berlin"));

  2. Creating a zoned DateTime and then converting it back to Date

    public static final ZoneId ZONE_EUROPE_BERLIN = ZoneId.of("Europe/Berlin");

@Override
public Date convertToDatabaseColumn(LocalDate locDate) {
    if (locDate == null) {
        return null;
    }

    ZonedDateTime zonedDateTime = locDate.atStartOfDay(ZONE_EUROPE_BERLIN);
    LocalDate producerLocalDate = zonedDateTime.toLocalDate();
    Date date = Date.valueOf(producerLocalDate);

    return date;
}

I used the second alternative.

david
  • 415
  • 1
  • 9
  • 21