Incorrect offset in DB for java.time.LocalDateTime

[xak2000]

I used JPA2.1 converters for my LocalDateTime fields to convert them to java.sql.Timestamp before save. All working fine until I decided to use hibernate-envers which is not support JPA2.1 converters. Then I switched to hibernate @Type annotation using jadira-usertypes extension.

First it seems working fine but then I notice then all my localdatetimes stored in DB with offset (+3h). When it retrieved it of course converted back (-3) and all seems to working fine. But it is not. It is wrong because LocalDateTime is designed to represent not instant in time but some local datetime for current place (whichever place you located at the moment). So when it saved to DB it must not use any timezone information. It must just be saved AS IS.

For example if I read my DB from another PHP application it must apply (-3) offset to all dates. Or if my database already filled with some data - all local dates will be wrong when read by this jadira mapper.

My DB isq Mysql with date type is DATETIME and the field represent exactly what LocalDateTime was designed for - start of working day - it is timezone agnostic. If Bob starts working at 8:00 it starts working at 8:00 in place where it working even if I observe he's working graphic from another timezone.

This bug also was found on this stackoverflow (not mine): http://stackoverflow.com/questions/31826867/how-to-setup-jadira-persistentlocaldatetime-with-java-time-localdatetime

[xak2000]

Setting

jadira.usertype.javaZone=Z
jadira.usertype.databaseZone=Z

or

jadira.usertype.javaZone=jvm
jadira.usertype.databaseZone=jvm

also doesn't help.
(It actually helps in my DEV enveronment with H2 databse, but doesn't help in my production environment with MySQL databse).

[xak2000]

In my project I created custom implementation of PersistentLocalDateTime and TimestampColumnLocalDateTimeMapper for it.

public class PersistentLocalDateTime
        extends AbstractVersionableUserType<LocalDateTime, Timestamp, TimestampColumnLocalDateTimeMapper>
        implements ParameterizedType, IntegratorConfiguredType {

    private static final long serialVersionUID = -2195437660203845282L;

    @Override
    public int compare(Object o1, Object o2) {
        return ((LocalDateTime) o1).compareTo((LocalDateTime) o2);
    }

    @Override
    public void applyConfiguration(SessionFactory sessionFactory) {

        super.applyConfiguration(sessionFactory);

        TimestampColumnLocalDateTimeMapper columnMapper = getColumnMapper();
        columnMapper.setDatabaseZone(null);
        //columnMapper.setJavaZone(null);
    }
}

public class TimestampColumnLocalDateTimeMapper extends AbstractTimestampThreeTenColumnMapper<LocalDateTime> implements DatabaseZoneConfigured<ZoneOffset> {

    private static final long serialVersionUID = -2085262909771406619L;

    public static final DateTimeFormatter LOCAL_DATETIME_FORMATTER = new DateTimeFormatterBuilder().appendPattern("yyyy-MM-dd HH:mm:ss").appendOptional(new DateTimeFormatterBuilder().appendLiteral('.').appendFraction(ChronoField.NANO_OF_SECOND, 1, 9, false).toFormatter()).toFormatter();

    public TimestampColumnLocalDateTimeMapper() {
        super();
    }

    public TimestampColumnLocalDateTimeMapper(ZoneOffset databaseZone) {
        super(databaseZone);
    }

    @Override
    public LocalDateTime fromNonNullString(String s) {
        return LocalDateTime.parse(s);
    }

    @Override
    public LocalDateTime fromNonNullValue(Timestamp value) {
        return value.toLocalDateTime();
    }

    @Override
    public String toNonNullString(LocalDateTime value) {
        return value.toString();
    }

    @Override
    public Timestamp toNonNullValue(LocalDateTime value) {
        return Timestamp.valueOf(value);
    }

    @Override
    public ZoneOffset parseZone(String zoneString) {
        return ZoneOffset.of(zoneString);
    }
}

This mapper works fine and correctly saves LocalDateTime to DB in both environments (DEV with H2 databse and PROD with MySQL).
I don't know if this correct approach and also some code may be redundant (overriding some methods, unused static field) but it works.

[chrisphe]

With MySQL it is recommended to pass the connection parameters "zeroDateTimeBehavior=convertToNull&useLegacyDatetimeCode=false". Can you confirm you are using these?

[xak2000]

I am using zeroDateTimeBehavior=convertToNull but not using useLegacyDatetimeCode=false. May it help? I will try later on monday.

[xak2000]

Yes, useLegacyDatetimeCode=false helps me. With this jdbc-connection setting org.jadira.usertype.dateandtime.threeten.PersistentLocalDateTime starts work as intended.
Thanks for help.