You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by "Andrus Adamchik (Jira)" <ji...@apache.org> on 2025/01/30 23:25:00 UTC

[jira] [Commented] (CAY-2701) MySQL DST-related LocalDateTime issues

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

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

I fixed this for MySQL 8 and newer. MySQL 5 will still add an hour during DST, but it is no longer a relevant DB to support. Note that the fix ended up being very clean. Instead of converting to/from String, we can use this:

{noformat}
rs.getObject(i, LocalDateTime.class);

st.setObject(pos, val, type);
{noformat}

So it no longer looks like hack.


> MySQL DST-related LocalDateTime issues
> --------------------------------------
>
>                 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
>             Fix For: 5.0-M2
>
>
> 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". This seems to be fixed in the 8.x driver, so this flag is no longer required there.
> _(Update for MySQL 8, driver version 9.1.0: This is still an issue, and an hour is still added. But_ _"noDatetimeStringSync=true"_ _is no longer required for the workaround described below - reading datetime as a String before converting it to a LocalDate)_
> 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.20.10#820010)