Java LocalDate is stored as a semi-random number of days back when saved to MySQL database

Question

I have an issue with saving correct Java LocalDates to a MySQL database. When I log the date, it shows the correct date, but when I save it for two different entities (User and Household),

Household always saves it as one day earlier (consistent and explainable by timezone diff), but
User saves a semi-random different date which I don't understand.

See image for examples of the correct date, household saved dat, user saved date and number of days difference between the user and household date can be seen in the following table. For the date 2017-04-01, the "random" version for the User entity even differs.

The dates are correct when running locally on my MacBook Pro, but the date differences occur in our Java 11 Google App Engine development or production environments. Could you help me understand why this is happening?

The date is received using a REST webservice that accepts a date as a ISO-formatted YYYY-MM-DD string as part of a UserRegistrationCommand class. This class then converts the string to a LocalDate object in its getMoveInDate method:

public class UserRegistrationCommand {

    ...
    public String moveInDate;
    ...

    public LocalDate getMoveInDate() {
        if(moveInDate == null) {
            return null;
        }
        try {
            return LocalDate.parse(moveInDate, DateTimeFormatter.ISO_LOCAL_DATE);
        } catch (Exception e) {
            return null;
        }
    }
}

Using Spring JPA, I then save this date for an User and Household entity respectively to a MySQL database where the tables for these entities are using a DATE datatype.

When I log the date prior to saving, it shows the correct value. But when the date is saved in the database, the User table gets a date many days off, while the Household table gets a date that is one day prior to the submitted date.

(The dates in the code comments below refer to the first date example in my image)

Saving the User entity:

@Transactional
public User createUser(String email, String password, String firstName, String lastName, String language, Household household, LocalDate moveInDate, String redirectState) {
    LOG.debug("Registering user {}, {}, moveInDate: {}", email, household, moveInDate != null ? moveInDate.toString() : "null"); // <-- Logs "Registering user ... moveInDate: 2017-08-01 ..."
    [...]
    Locale locale = localeProvider.matchAvailableLocale(language);
    User user = new User(email, encodedPassword, firstName, lastName, locale, household, moveInDate, NotificationPreferences.defaultPreferences());
    user.setRedirectState(redirectState);
    Image profilePicture = avatarService.createProfilePicture(user.getNameInitials());
    user.setProfilePicture(profilePicture);
    return save(user); // <-- calls userRepository.save(user), but date is stored as 2017-07-27
}

Saving the household entity:

LOGGER.debug("ActivationCodeRegistrationStrategy.register: Setting moveInDate {} for household {} for user {}", cmd.getMoveInDate(), activationCode.getHousehold(), cmd.email); <-- logs "ActivationCodeRegistrationStrategy.register: Setting moveInDate 2017-08-01 ..."

householdService.setMoveInDate(activationCode.getHousehold(), cmd.getMoveInDate()); <-- but a date of 2017-07-31 is saved.

The householdService.setMoveInDate call just sets the date for the Household entity and saves it to database:

public void setMoveInDate(Household household, LocalDate moveInDate) {
    household.setMoveInDate(moveInDate);
    householdRepository.save(household);
}

Java LocalDate is stored as a semi-random number of days back when saved to MySQL database

Answers (1)

Related Questions