when I fetch a DateTime from MySQL database using Spring, the received DateTime is increased by 3 hours.

What is the reason for that and how to solve this issue? I want the same date and time as it is in db.

In model:

@Column 
private LocalDateTime dateAndTime; 

Converters:

import java.sql.Timestamp;
import java.time.LocalDateTime;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true)
public class LocalDateTimeAttributeConverter implements AttributeConverter<LocalDateTime, Timestamp> {
    
    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime locDateTime) {
        return (locDateTime == null ? null : Timestamp.valueOf(locDateTime));
    }

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

In MySQL:

left => column name, right => datatype in mysql

the time is changed by 3 hours when received the data from DB...

EDIT: I recognized that also when I save a datetime to the database the passed time is decreased by 3 hours and then saved in db... very strange.

I checked time zone infos:

• the column says timezone: 0300
• I have set up Spring to set timezone at startup to GMT 3

When I change the timezone in Spring to GMT 0 then the same time as in DB is returned...but when I save a dateTime to the db is is decreased by 3 hours...very strange.

Any Ideas?

CodePudding user response：

It looks to me like your date_and_time column is already stored in EEST or some other UTC 3 timezone. So, .toLocalDateTime() pushes it forward another three hours.

CodePudding user response：

I found the issue...

in my datasource I had

...?&useLegacyDatetimeCode=false&serverTimezone=UTC&useUnicode=yes&character_set_server=utf8mb4

see the serverTimezone=UTC ... just removed it and it works...