[jira] [Commented] (CAY-2701) DST-related conflicts with LocalDateTime

["Andrus Adamchik (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CAY-2701?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17304064#comment-17304064 ] 

Andrus Adamchik commented on CAY-2701:
--------------------------------------

Oh, and here is an ExtendedType that I am using. It has some MySQL-specific code (timestamp String format), and doesn't handle fractional seconds.

{noformat}
public class LocalDateTimeType implements ExtendedType<LocalDateTime> {

    static final DateTimeFormatter MYSQL_LOCAL_DATE_TIME = new DateTimeFormatterBuilder()
                .parseCaseInsensitive()
                .append(DateTimeFormatter.ISO_LOCAL_DATE)
                .appendLiteral(' ')
                .append(DateTimeFormatter.ISO_LOCAL_TIME)
                .toFormatter();

    @Override
    public String getClassName() {
        return LocalDateTime.class.getName();
    }

    @Override
    public void setJdbcObject(PreparedStatement statement, LocalDateTime value, int pos, int type, int scale) throws Exception {
        if (value == null) {
            statement.setNull(pos, type);
        } else {
            // using ISO-8601 formatted String.. This works with MySQL... Converting to Timestamp and using "setTimestamp"
            // doesn't work, as Timestamp.valueOf(LocalDateTime) will not properly handle the DST "skipped hour" if the
            // current Java TZ has a DST
            statement.setString(pos, value.toString());
        }
    }

    @Override
    public LocalDateTime materializeObject(ResultSet rs, int index, int type) throws Exception {
        // On MySQL this requires "noDatetimeStringSync=true", otherwise the driver will first create a Timestamp,
        // that will mess up the DST settings, and then convert that Timestamp to a (possibly incorrect) String
        String ts = rs.getString(index);
        return ts != null ? LocalDateTime.parse(ts, MYSQL_LOCAL_DATE_TIME) : null;
    }

    @Override
    public LocalDateTime materializeObject(CallableStatement rs, int index, int type) throws Exception {
        String ts = rs.getString(index);
        return ts != null ? LocalDateTime.parse(ts, MYSQL_LOCAL_DATE_TIME) : null;
    }

    @Override
    public String toString(LocalDateTime value) {
        return value != null ? '\'' + value.toString() + '\'' : "NULL";
    }
}
{noformat}

> DST-related conflicts with LocalDateTime
> ----------------------------------------
>
>                 Key: CAY-2701
>                 URL: https://issues.apache.org/jira/browse/CAY-2701
>             Project: Cayenne
>          Issue Type: Bug
>    Affects Versions: 4.0.2, 4.1, 4.2.M2
>         Environment: MySQL 5.7.x, table column with type "datetime"
> JDBC Driver: mysql:mysql-connector-java:5.1.46
> JVM timezone: "America/New_York"
>            Reporter: Andrus Adamchik
>            Priority: Major
>
> Just ran into a whole collection of annoying problems related to daylight-savings time. As mentioned in the "Environment" section, the test environment is MySQL 5.7.x, mysql:mysql-connector-java:5.1.46, "America/New_York" timezone. Some of the issues described here are universal, others are DB and driver and JVM TZ sensitive.
> h2. Problem 1: Lossy conversion from LocalDateTime to Timestamp
> Say a LocalDateTime value corresponds to a UTC timezone (so no DST there), and want to save a value of "2021-03-14T02:00:00". The JVM is located in "America/New_York" timezone, where this specific hour ("2021-03-14T02:XX:XX") is skipped due to EST to EDT switchover. This combination prevents Cayenne from saving such as local date correctly because LocalDateTime to Timestamp conversion (used by Cayenne to bind datetime value to JDBC) would actually use the JVM TZ (!!) and the hour will be increased by 1 (so "2021-03-14T03:35:00" will be saved). Here is a JDBC-agnostic test to demonstreate that:
> {noformat}
> @Test
> public void test() {
>     TimeZone defaultTz = TimeZone.getDefault();
>     TimeZone.setDefault(TimeZone.getTimeZone("America/New_York"));
>     try {
>         LocalDateTime dt = LocalDateTime.parse("2021-03-14T02:35:00");
>         assertEquals(dt, Timestamp.valueOf(dt).toLocalDateTime());
>     } finally {
>         TimeZone.setDefault(defaultTz);
>     }
> }
> {noformat}
> There seems to be on way around it (this is an expected java.sql.Timestamp behavior!!), except to replace LocalDateTime-to-Timestamp conversion with LocalDateTime-to-String conversion. This causes some downstream driver issues. MySQL 5.1.x driver throws on "PreparedStatement.setObject(i, string, Types.TIMESTAMP)", and "setString(..)" should be called instead. Not sure about other DBs and 8.x MySQL driver.
> With the above in mind, LocalDateTime ValueObjectType should be reimplemented as an ExtendedType, and we need to test it across the DBs.
> h2. Problem 2: MySQL 5.1.x driver will add an hour on read
> The ExtendedType above allows to write LocalDateTime properly, DST or not. But when reading it back, MySQL Driver interferes. When reading a column that is a "datetime" as String, it first does a conversion to Timestamp, and then converts it to String. So again - an hour is added unexpectedly.
> There's no Cayenne-side fix for that. But the DB connection string must contain "noDatetimeStringSync=true". And this seems to be fixed in the 8.x driver. 
> h2. Workarounds / Best Practices
> While we need to address this craiziness with a new ExtendedType, I suspect if a user sets his Java server TZ to UTC, they should avoid all this insanity. Though of course YMMV when e.g. running unit tests in a specific TZ you may run into this problem.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)