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);
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();