[
https://issues.apache.org/jira/browse/CAY-2701?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Andrus Adamchik updated CAY-2701:
---------------------------------
Description:
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 with or without_ _"noDatetimeStringSync=true"_ _)_
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.
was:
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.
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.
> 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 with or without_ _"noDatetimeStringSync=true"_ _)_
> 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)
