Connector/J version 8.0.23 came out with several bug fixes related to date-time types support. They provide more flexibility for configuring time zone handling and allow migration from Connector/J 5.1 with much less effort.
Problems with migration from Connector/J 5.1 to Connector/J 8.0 were caused by the early decision that Connector/J 8.0 should always try to preserve an instant point on the time-line while Connector/J 5.1 does it optionally and, by default, preserves the original visual representation.
For example, the following code will store different results with Connector/J 5.1 and Connector/J 8.0 in case the client and server time zones are different:
Statement st = conn.createStatement();
st.executeUpdate("CREATE TABLE t1 (ts TIMESTAMP)");
PreparedStatement ps = conn.prepareStatement("INSERT INTO t1 VALUES (?)");
ps.setTimestamp(1, Timestamp.valueOf("2020-01-01 12:00:00"));
ps.executeUpdate();
If the client is running in the UTC+2 time zone and server is running in UTC+1 the internal value of the TIMESTAMP field will be “2020-01-01 11:00:00Z” with Connector/J 5.1 but “2020-01-01 10:00:00Z” with Connector/J 8.0.
Another client in the UTC+3 time zone is reading this value:
ResultSet rs = st.executeQuery("SELECT * FROM t1");
Timestamp ts = rs.getTimestamp(1);
The result will be “2020-01-01 12:00:00” with Connector/J 5.1 but “2020-01-01 13:00:00” with Connector/J 8.0.
By default, Connector/J 5.1 sends values as they are rendered locally and, on retrieval, constructs values using the client’s local time zone. Thus, the visual representation remains the same in any client time zone and on the server. But the internal UTC value of a TIMESTAMP could be different from an expected instant value.
Connector/J 8.0.22 and before converts the original value to the session time zone before sending, thus the internal UTC value of a TIMESTAMP matches the expected instant value. When retrieved, a value is constructed after converting the on-wire value from session time zone to the local one, so it still represents the same instant, but the visual representation is different in different client time zones.
Actually both approaches are valid use cases. You probably don’t care about the real internal TIMESTAMP value if you don’t do any server-side calculations with it. But you might want to store the instant point on the time-line, like for a Zoom meeting start time, in other cases. Both ways are now possible with Connector/J 8.0.23. The following connection properties define the time zone handling:
- connectionTimeZone=LOCAL|SERVER|user-defined time zone (previously known as ‘serverTimezone’, now with additional fixed values) defines how the server’s session time zone is to be determined by Connector/J.
- forceConnectionTimeZoneToSession=true|false controls whether the session time_zone variable is to be set to the value specified in ‘connectionTimeZone’.
- preserveInstants=true|false turns on|off the conversion of instant values between JVM and ‘connectionTimeZone’.
The most useful configurations are:
- connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=false – corresponds with the Connector/J 5.1 behavior with useLegacyDatetimeCode=true.
- connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=true – the new mode which provides the most natural way for handling date-time values.
- connectionTimeZone=SERVER & preserveInstants=true – corresponds to the previous Connector/J 8.0 behavior and Connector/J 5.1 behavior with useLegacyDatetimeCode=false.
- connectionTimeZone=user_defined & preserveInstants=true – helps to overcome the situation when the server time zone cannot be recognized by the connector because it is set as a generic abbreviation like CET/CEST.
More details are given below, but first let’s define what exactly we mean when talking about “instant date-time classes” and “instant MySQL types”.
Supported MySQL date-time data types
The MySQL TIMESTAMP is the only data type designed to store instant points on the time-line using the implied time zone conversion. Incoming values are converted by server from the session time zone to UTC for storage, and outgoing values are converted from UTC to the session time zone. There is no way to preserve the original time zone but even if the session time zone was altered, the retrieved value still represents the same instant point on the time-line. Starting from MySQL 8.0.19, you can also specify a time zone offset when storing TIMESTAMP values (see “The DATE, DATETIME, and TIMESTAMP Types”). In such case the incoming values are converted to UTC from the specified offset instead of the session time zone. But, finally, the original offset is also lost.
The MySQL YEAR represents only the year number, DATE is missing a time part, TIME is missing the date part. So, they are definitely not instant types.
The situation with MySQL DATETIME data type is less straightforward. There is no implied time zone conversion for this type; values are stored and retrieved as they are, so this type could be taken as an analog of Java LocalDateTime. However, with the new syntax available since MySQL 8.0.19, when a value with offset is stored to a DATETIME column, it is converted to the session time zone!
New-style DATETIME values are consistent with old-style ones and the retrieved results are consistent only when client’s and session time zones are the same.
Therefore, it is more correct to say that server assumes that local date-time values coming to DATETIME always belong to the session time zone. In any case, DATETIME is not an instant type: there is no time zone information associated with a stored DATETIME value.
Users sometimes store Timestamps to DATETIME instead of TIMESTAMP because of wider ranges of represented values (‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ against ‘1970-01-01 00:00:01.000000Z’ to ‘2038-01-19 03:14:07.999999Z’ respectively). It is possible to use DATETIME and still leverage the capability of preserving instants but with some limitations. See details in preserveInstants property description below.
Supported Java date-time classes
Connector/J 8.0.23 considers following date-time classes as “non-instant” ones even when some of them are extending the java.util.Date:
- java.sql.Date – The time components are set to zeros.
- java.sql.Time – The date components are set to the “zero epoch” value.
- java.time.LocalDate – The time components and time zone are not defined.
- java.time.LocalTime – The date components and time zone are not defined.
- java.time.LocalDateTime – The time zone is not defined.
- java.time.OffsetTime – The date components are not defined.
Supported “instant” date-time classes are:
- java.util.Calendar
- java.util.Date
- java.sql.Timestamp
- java.time.OffsetDateTime
- java.time.ZonedDateTime
Preserving instants
First, let’s enumerate the time zones we are dealing with:
- MySQL TIMESTAMP internal time zone– it’s always UTC.
- MySQL session time zone – the per-client “on-wire” time zone, specified in the time_zone system variable. By default, the initial value of this is ‘SYSTEM’, which means, “use the value of system_time_zone”, but it may be redefined at server startup with the –default-time-zone option.
- Client local time zone – the JVM default time zone. By default, it is equal to the client system time zone but can be changed by an application.
- Original time zone – Objects based on java.util.Date are associated with the JVM time zone by default, but java.util.Calendar, java.time.OffsetDateTime and java.time.ZonedDateTime have an explicit time zone.
So, MySQL never stores a date-time value with its original time zone but it always assumes that this value is correctly represented in the session time zone. It means that in order to preserve the instant value, Connector/J should adjust it to the session time zone before sending to server. It can be done in different ways. Either Connector/J converts the value from the original time zone to the session time zone and then sends it, or Connector/J could set the session time zone equal to the local one so that no conversion is needed then except for java.util.Calendar, java.time.OffsetDateTime, and java.time.ZonedDateTime values, which should be converted to the local time zone.
Connector/J will never try to preserve the instant value if either the source class or the target type are not of the instant-based ones, even if Connector/J is configured to preserve instant. For example, if java.sql.Date is sent as SQL TIMESTAMP, it is rendered in the local time zone, if java.sql.Timestamp is sent as SQL DATE it is also rendered in the local time zone. But when java.sql.Timestamp is sent as SQL TIMESTAMP the value will be adjusted to the session time zone.
For example, with client time zone UTC+2, session time zone UTC+1 and connection is created with connectionTimeZone=SERVER&preserveInstants=true:
import com.mysql.cj.MysqlType;
...
ps = conn.prepareStatement("INSERT INTO t1 VALUES (?)");
ps.setObject(Date.valueOf("2020-01-01"), MysqlType.DATE);
ps.executeUpdate(); // sends "INSERT INTO t1 VALUES ('2020-01-01')"
ps.setObject(Date.valueOf("2020-01-01"), MysqlType.TIMESTAMP);
ps.executeUpdate(); // sends "INSERT INTO t1 VALUES ('2020-01-01 00:00:00')"
ps.setObject(Timestamp.valueOf("2020-01-01 00:00:00"), MysqlType.TIMESTAMP);
ps.executeUpdate(); // sends "INSERT INTO t1 VALUES ('2019-12-31 23:00:00')"
Please note, that the target type is not necessarily the real target column type. Connector/J has two implementations of PreparedStatement. ServerPreparedStatement is really prepared on the server side and returns the parameters’ metadata on prepare. But the ClientPreparedStatement is prepared on the client side and sent as a plain query on execute, thus no parameters metadata is available there. Since Connector/J could switch between these implementations internally on some conditions, they should perform identically. Thus, for the sake of consistency, the parameters metadata is not used, the decision about sent value is always based only on the default JDBC Type for the used Java class, as defined in TABLE B-4 of the JDBC specification, or on the explicitly defined target JDBC Type in a setObject() call.
MySQL DATETIME is a non-standard type but it matches perfectly with the LocalDateTime class by nature, thus the default target type for LocalDateTime is MysqlType.DATETIME instead of TIMESTAMP, as per the JDBC specification.
Time zone configuration properties
- connectionTimeZone=LOCAL|SERVER|user-defined time zone. The ‘serverTimezone’ property was renamed to ‘connectionTimeZone’ in order to highlight that it does not necessary match the real server or session time zone. It just informs the connector which time zone should be used in cases where the instant value must be converted between the JVM and the on-wire time zones. If it is set to “LOCAL” (which is the default value, if the property is not specified) the driver assumes that the connection time zone is the same as the JVM default time zone. If set to “SERVER” then the driver attempts to detect the session time zone from the values configured on the MySQL server session variables “time_zone” or “system_time_zone”. An explicit value must be a geographic time zone name or a time zone offset from Greenwich/UTC, using a syntax java.time.ZoneId is able to parse.
This option itself does not set MySQL server session variable ‘time_zone’ to the given value. To do that the ‘forceConnectionTimeZoneToSession’ connection option must be set to “true”.
Please note that setting a value to ‘connectionTimeZone’ in conjunction with ‘forceConnectionTimeZoneToSession=false’ and ‘preserveInstants=false’ has no effects since, in that case, connectionTimeZone is used neither to change the session time zone nor to convert the time zone.
- forceConnectionTimeZoneToSession=true|false. If it is set to “false” (which is the default value) the session time zone is left unchanged on the server. When this property is set to “true”, the driver sets the time zone value determined by ‘connectionTimeZone’ connection property to the current server session ‘time_zone’ variable.
Please be aware that altering the session time zone also affects the result of MySQL functions such as ‘NOW()’, ‘CURTIME()’ or ‘CURDATE()’.
This option has no effect if used in conjunction with ‘connectionTimeZone=SERVER’ since, in this case, you are requesting the session time zone to be set to the value it already has.
- preserveInstants=true|false. With ‘preserveInstants=false’ Connector/J 8.0.23 always uses the JVM default time zone for rendering the values it sends to the server and for constructing the Java objects from the fetched data. It matches the default Connector/J 5.1 behavior. If ‘preserveInstants=true’ (which is the default value), Connector/J does its best to preserve the instant point on the time-line for Java instant-based objects such as java.sql.Timestamp or java.time.OffsetDateTime, instead of preserving the time’s original visual form. When storing, the conversion is performed only if the target SQLType, either the explicit one or the default one, is TIMESTAMP. When retrieving, the conversion is performed only if the source column has the TIMESTAMP, DATETIME or character type and the target class is an instant-based one, like java.sql.Timestamp or java.time.OffsetDateTime.
This option has no effect if used in conjunction with ‘connectionTimeZone=LOCAL’ since, in this case, the source and target time zones are the same.
Conversion between original time zone and session time zone has a risk of altering the value in case they have incompatible DST rules.
Time zone configurations
- connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=false. No time zone conversion is done by the driver because it assumes that the session time zone equals to the local one. The actual session time zone is left untouched and it is the same for all clients. That’s the way for keeping the same visual date-time value representation. Instants may be kept correctly only when all clients and the server are actually residing in the same time zone.
This configuration corresponds to the default Connector/J 5.1 behavior (with useLegacyDatetimeCode=true).
- connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=true. This new mode provides the most natural way for handling date-time values. Connector/J sets the session time zone equal to the local one and always uses the local time zone internally. Server converts a TIMESTAMP to its internal UTC value directly from the client time zone eliminating possible problems with DST. It’s also safe to use both the old syntax and the extended syntax with zone offset for setting DATETIMEs–they are finally adjusted to the same session time zone.
Since different clients in this mode might change their individual session into different time zones, this mode does not allow the same visual date-time value representation to be kept in a TIMESTAMP column; store the value to a DATETIME column instead.
And, again, altering the session time zone also affects the result of MySQL functions such as ‘NOW()’, ‘CURTIME()’ or ‘CURDATE()’.
- connectionTimeZone=SERVER & preserveInstants=true. The session time zone is left untouched and it is the same for all clients. The driver converts instant values to the session time zone when they are sent as a TIMESTAMP. When retrieving, the driver converts values from the session time zone to the local one if the source column is in the TIMESTAMP, DATETIME or character type and the target class is an instant-based one.
This configuration corresponds to the previous Connector/J 8.0 behavior and Connector/J 5.1 behavior with useLegacyDatetimeCode=false.
- connectionTimeZone=user_defined & preserveInstants=true. Without changing the session time zone on the server, this setup helps to overcome the situation in which the server time zone cannot be recognized by the connector because it is set with an unrecognizable, generic abbreviation like CET or CEST. This configuration gives Connector/J the proper time zone identification for the session, to which the timestamps should be converted.
OffsetDateTime and ZonedDateTime support
The original time zone of java.time.OffsetDateTime and java.time.ZonedDateTime is never preserved when they are stored to the date-time column. The driver first converts the date-time values into the local time zone and then, if it is preserving instants, it converts them to the session time zone.
When the driver reads instants from the server into these objects and it is configured to preserve instants, the result will be constructed with the session time zone. Otherwise, result will be constructed with the local time zone.
For example, with client time zone UTC+2, session time zone UTC+1 and the connection created with connectionTimeZone=SERVER&preserveInstants=true:
import com.mysql.cj.MysqlType;
...
ps = conn.prepareStatement("INSERT INTO t1 VALUES (?)");
ps.setObject(OffsetDateTime.parse("2020-01-01T13:00:00+03:00"));
ps.executeUpdate();
// sends "INSERT INTO t1 VALUES ('2020-01-01 11:00:00')"
ResultSet rs = st.executeQuery("SELECT * FROM t1");
String odt = rs.getObject(1, OffsetDateTime.class).toString();
// returns "2020-01-01T11:00:00+01:00"
Fractional seconds support
New connection property sendFractionalSecondsForTime=true|false tells the driver to send or ignore the fractional seconds contained in java.sql.Time.
This option overrides the inconsistency between MySQL TIME and JDBC java.sql.Time. While MySQL TIME may store fractional seconds the java.sql.Time is defined as a time value without a fractional part. On the other hand, java.sql.Time is a wrapper around the java.util.Date, thus internally it also may contain fractional seconds. So, when sending the java.sql.Time with ‘sendFractionalSecondsForTime=true’, its value is rendered with fractional seconds; with ‘sendFractionalSecondsForTime=false’, the value is rendered without fractional seconds.
This option is not applied to getters–values from MySQL TIME are always constructed with a fractional part if provided by server.
Connection property sendFractionalSeconds=true|false is redefined as a global switch. If set to “false”, fractional seconds are rounded or truncated not only for TIMESTAMP but also for any other date-time type.