[
https://issues.apache.org/jira/browse/CAY-2701?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17304064#comment-17304064
]
Andrus Adamchik edited comment on CAY-2701 at 3/18/21, 11:35 AM:
-----------------------------------------------------------------
Oh, and here is an ExtendedType that I am using. It has some MySQL-specific
code (timestamp String format)
{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}
was (Author: andrus):
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)
