6.6.1 Preserving Time Instants

Background

A time instant is a specific moment on a time-line. A time instant is said to be preserved when it always refers to the same point in time when its value is being stored to or retrieved from a database, no matter what time zones the database server and the clients are operating in.

TIMESTAMP is the only MySQL data type designed to store instants. To preserve time instants, the server applies time zone conversions in incoming or outgoing time values when needed. Incoming values are converted by server from the connection session's time zone to Coordinated Universal Time (UTC) for storage, and outgoing values are converted from UTC to the session time zone. 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 for details), in which case the TIMESTAMP values are converted to the UTC from the specified offset instead of the session time zone. But, once stored, the original offset information is no longer preserved.

The situation is less straightforward with the DATETIME data type: it does not represent an instant and, when no time zone offset is specified, there is no time zone conversion for DATETIME values, so they are stored and retrieved as they are. However, with a specified time zone offset, the input value is converted to the session time zone before it is stored; the result is that, when retrieved in a different session with a different time zone offset as the specified one, the DATETIME value becomes different from the original input value.

Because MySQL data types other than TIMESTAMP (and the Java wrapper classes for those other MySQL data types) do not represent true time instants; mixing up instant-representing and non-instant-representing date-time types when storing and retrieving values might give rise to unexpected results. For example:

• When storing java.sql.Timestamp to, for example, a DATETIME column, you might not get back the same instant value when retrieving it into a client that is in a different time zone than the one the client was in when storing the value.

• When storing, for example, a java.time.LocalDateTime to a TIMESTAMP column, you might not be storing the correct UTC-based value for it, because the time zone for the value is actually undefined.

Therefore, do not pass instant date-time types (java.util.Calendar, java.util.Date, java.time.OffsetDateTime, java.sql.Timestamp) to non-instant date-time types (for example, java.sql.DATE, java.time.LocalDate, java.time.LocalTime, java.time.OffsetTime) or vice versa, when working with the server.

The rest of the section discusses how to preserve time instants when working with Connector/J.

Preserving Instants with Connector/J

The scenario: Let us assume that an application is running on a certain application server and is connecting to a MySQL server using Connector/J. Certain events take place in a connection session, for which timestamps are generated, and the event timestamps are associated with the JVM time zone of the application server. These timestamps are to be stored onto a MySQL Server, and are also to be retrieved from it later.

The challenge: The timestamps' instant values need to be preserved when they are saved onto or retrieved from the server using Connector/J. Because the MySQL Server always assumes implicitly that a time instant value references to the connection session time zone (which is set by the session time_zone variable ) when being saved to or retrieved form the server, a time instant value is properly preserved only in the following situations:

1. When Connector/J is running in the same time zone as the MySQL Server (i.e., the server's session time zone is the same as the JVM's time zone), time instants are naturally preserved, and no time zone conversion is needed. Note that in this case, time instants are really preserved only if the server and the JVM continue to run always in the same time zone in the future.

2. When Connector/J is running in a different time zone from that of the MySQL Server (i.e., the JVM's time zone is different from the server's session time zone), Connector/.J performs one of the following:

   1. Queries the value of the session time zone from the server, and converts the event timestamps between the session time zone and the JVM time zone.

   2. Changes the server's session time zone to that of the JVM time zone, after which no time zone conversion will be required.

   3. Changes the server session time zone to a desired time zone specified by the user, and then converts the timestamps between the JVM time zone and the user-specified time zone.

We identify the above solutions for time instant preservation as Solution 1, 2a, 2b, and 2c. To achieve these solutions, the following connection properties have been introduced in Connector/J since release 8.0.23:

• preserveInstants={true|false}: Whether to attempt to preserve time instant values by adjusting timestamps.

  • When it is false, no conversions are attempted; a timestamp is sent to the server as-is for storage, and its visual presentation, not the actual time instant is preserved. When it is retrieved from the server by Connector/J, different time zones might be associated with it, as the retrieval might happen in different JVM time zones. For example: For example:

    • Time zones: UTC for JVM, UTC+1 for server session

    • Original timestamp from client (in UTC): 2020-01-01 01:00:00

    • Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (no conversion)

    • Timestamp values stored internally on the server: 2020-01-01 00:00:00 UTC (after internal conversion of 2020-01-01 00:00:00 UTC+1 to UTC)

    • Timestamp value retrieved later into a server section (in UTC+1): 2020-01-01 01:00:00 (after internal conversion of 2020-01-01 00:00:00 from UTC to UTC+1)

    • Timestamp values constructed by Connector/J in some other JVM time zone then before (say, in UTC+3): 2020-01-01 01:00:00

    • Comment: Time instant is not preserved

  • When it is true, Connector/J attempts to preserve the time instants by performing the conversions in a manner defined by the connection properties connectionTimeZone and forceConnectionTimeZoneToSession.

    When storing a value, the conversion is performed only if the target data type, either the explicit one or the default one, is TIMESTAMP. When retrieving a value, the conversion is performed only if the source column has the TIMESTAMP, DATETIME, or a character data type and the target class is an instant-preserving one, like java.sql.Timestamp or java.time.OffsetDateTime.

• connectionTimeZone={LOCAL|SERVER|user-defined-time-zone}: Specifies how the server's session time zone (in reference to which the timestamps are saved onto the server) is to be determined by Connector/J. It takes on one of the following values:

  • LOCAL: Connector/J assumes that the server's session time zone either (a) is the same as the JVM time zone for Connector/J, or (b) should be set as the same as the JVM time zone for Connector/J. Connector/J takes the situation as (a) or (b) depending on the value of the connection property forceConnectionTimeZoneToSession.

  • SERVER: Connector/J should query the session's time zone from the server, instead of making any assumptions about it. If the session time zone actually turns out to be different from Connector/J's JVM time zone and preserveInstants=true, Connector/J performs time zone conversion between the session time zone and the JVM time zone.

  • user-defined-time-zone: Connector/J assumes that the server's session time zone either (a) is the same as the user-defined time zone, or (b) should be set as the user-defined time zone. Connector/J takes the situation as (a) or (b) depending on the value of the connection property forceConnectionTimeZoneToSession.

  Note

  For Connector/J 8.0.23 and later, serverTimezone is an alias for connectionTimeZone. For Connector/J 8.0.22 and earlier, serverTimezone was used to override the session time zone setting on the server.

• forceConnectionTimeZoneToSession={true|false}: Controls whether the session time_zone variable is to be set to the value specified in connectionTimeZone.

Now, here are the connection properties values to be used for achieving the Solutions defined above for preserving time instants:

• Solution 1: Use either preserveInstants=false or connectionTimeZone=LOCAL& forceConnectionTimeZoneToSession=false. Because it can be safely assumed that the server session time zone is the same as Connector/J' s JVM timezone, no query of the server's session time zone occurs, and no time zone conversion occurs. For example:

  • Time zones: UTC+1 for both the JVM and the server session

  • Original timestamp from client (in UTC+1): 2020-01-01 01:00:00

  • Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (no conversion needed)

  • Timestamp values stored internally on the server: 2020-01-01 00:00:00 UTC (after internal conversion from UTC+1 to UTC)

  • Timestamp value retrieved later into a server time session in UTC+1 that Connector/J connects to: 2020-01-01 01:00:00 (after internal conversion from UTC to UTC+1)

  • Timestamp value constructed by Connector/J in the same JVM time zone as before (UTC+1) and returned to an application: 2020-01-01 01:00:00

  • Comment: Time instant is preserved without conversion.

  Note

  This setting corresponds to the default behavior of Connector/J 5.1

• Solution 2a: Use preserveInstants=true&connectionTimeZone=SERVER . Connector/J then queries the value of the session time zone from the server, and converts the event timestamps between the session time zone and the JVM time zone. For example:

  • Time zones: UTC+2 for JVM, UTC+1 for server session

  • Original timestamp from client (in UTC+2): 2020-01-01 02:00:00

  • Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (after conversion from UTC+2 to UTC+1)

  • Timestamp value stored internally on the server: 2020-01-01 00:00:00 UTC (after internal conversion from UTC+1 to UTC)

  • Timestamp value retrieved later into a server session in UTC+1: 2020-01-01 01:00:00 (after internal conversion from UTC to UTC+1)

  • Timestamp values constructed by Connector/J in the same JVM time zone as before (UTC+2) and returned to an application: 2020-01-01 02:00:00 (after conversion from UTC+1 to UTC+2)

  • Timestamp values constructed by Connector/J in another JVM time zone (say, UTC+3) and returned to an application: 2020-01-01 03:00:00 (after conversion from UTC+1 to UTC+3)

  • Comment: Time instant is preserved.

  Notes

  • This setting corresponds to the default behavior of Connector/J 8.0.22 and before and to the behavior of Connector/J 5.1 with useLegacyDatetimeCode=false.

• Solution 2b: Use connectionTimeZone=LOCAL& forceConnectionTimeZoneToSession=true. Connector/J then changes the server's session time zone to that of the JVM time zone, after which no timezone conversions are required when storing or achieving the timestamps. For example:

  • Time zones: UTC+1 for JVM, UTC+2 for server session originally, but now modified to UTC+1 by Connector/J

  • Original timestamp from client (in UTC+1): 2020-01-01 01:00:00

  • Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (no conversion)

  • Timestamp values stored internally on the server: 2020-01-01 00:00:00 (after internal conversion from UTC+1 to UTC)

  • Timestamp values retrieved later into a server session (in UTC+1, as set by Connector/J): 2020-01-01 01:00:00 (after internal conversion from UTC to UTC+1)

  • Timestamp value constructed by Connector/J in the same JVM time zone as before (UTC+1): 2020-01-01 01:00:00 (no conversion needed)

  • Timestamp values retrieved later into a server session (time zone modified to, say, UTC+3, by Connector/J): 2020-01-01 03:00:00 (after internal conversion from UTC to UTC+3)

  • Timestamp value constructed by Connector/J in the JVM time zone of UTC+3: 2020-01-01 03:00:00 (no conversion needed)

  • Comment: Time instant is preserved without conversion by Connector/J, because the session time zone is changed by Connector/J to its JVM's value.

  Warnings

  • • Altering the session time zone affects the results of MySQL functions such as NOW(), CURTIME(), or CURDATE()—if you do not want those functions to be affected, do not use this setting.

    • If you use this setting on different clients in different time zones, the clients are going to modify their connection session's time zones to different values; if you want to keep the same visual date-time value representation for the same time instant for all the clients and in all their sessions, store the values to a DATETIME instead of a TIMESTAMP column and use non-instant Java classes for them, for example, java.time.LocalDateTime.

• Solution 2c: Use preserveInstants=true&connectionTimeZone=user-defined-time-zone& forceConnectionTimeZoneToSession=true. Connector/J then changes the server's session time zone to the user-defined time zone, and converts the timestamps between the user-defined time zone and the JVM time zone. A typical use case for this setting is when the session time zone value on the server is known to be unrecognizable by Connector/J (e.g., CST or CEST). For example:

  • Time zones: UTC+2 for JVM, CET for server session originally, but now modified to user-specified Europe/Berlin by Connector/J

  • Original timestamp from client (in UTC+2): 2020-01-01 02:00:00

  • Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (after conversion between JVM time zone (UTC+2) and user-defined time zone (Europe/Berlin=UTC+1))

  • Timestamp values stored internally on the server: 2020-01-01 00:00:00 (after internal conversion from UTC+1 to UTC)

  • Timestamp value retrieved into a server session (time zone modified to Europe/Berlin (=UTC+1) by Connector/J): 2020-01-01 01:00:00 (after internal conversion from UTC to UTC+1)

  • Timestamp value constructed by Connector/J in the same JVM time zone as before (UTC+2) and returned to an application: 2020-01-01 02:00:00 (after conversion between user-defined time zone (UTC+1) and JVM time zone (UTC+2)).

  • Comment: Time instant is preserved with conversion and with the session time zone being changed by Connector/J according to a user-defined value.

  As an alternative to this solution, the user might want the same conversion of the timestamps between the JVM time zone and the user-defined time zone as described above, without actually correcting the unrecognizable time zone value on the server. To do so, use, preserveInstants=true&connectionTimeZone=user-defined-time-zone& forceConnectionTimeZoneToSession=false. This achieves the same result of preserving the time instant.

  Warnings

  See the warnings above for Solution 2b.