Documentation Home
Connectors and APIs Manual
Download this Manual

3.5.3.11 Datetime types processing

  • connectionTimeZone

    Configures the connection time zone which is used by Connector/J if conversion between the JVM default and a target time zone is needed when preserving instant temporal values.

    Accepts a geographic time zone name or a time zone offset from Greenwich/UTC, using a syntax 'java.time.ZoneId' is able to parse, or one of the two logical values "LOCAL" and "SERVER". Default is "LOCAL". If set to an explicit time zone then it must be one that either the JVM or both the JVM and MySQL support. If set to "LOCAL" then 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'. The time zone detection and subsequent mapping to a Java time zone may fail due to several reasons, mostly because of time zone abbreviations being used, in which case an explicit time zone must be set or a different time zone must be configured on the server.

    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 effect since, in this case, neither this option is used to change the session time zone nor used for time zone conversions of time-based data.

    Former connection option 'serverTimezone' is still valid as an alias of this one but may be deprecated in the future.

    See also 'forceConnectionTimeZoneToSession' and 'preserveInstants' for more details.

    Since Version 3.0.2
  • forceConnectionTimeZoneToSession

    If enabled, sets the time zone value determined by 'connectionTimeZone' connection property to the current server session 'time_zone' variable. If the time zone value is given as a geographical time zone, then Connector/J sets this value as-is in the server session, in which case the time zone system tables must be populated beforehand (consult the MySQL Server documentation for further details); but, if the value is given as an offset from Greenwich/UTC in any of the supported syntaxes, then the server session time zone is set as a numeric offset from UTC.

    With that no intermediate conversion between JVM default time zone and connection time zone is needed to store correct milliseconds value of instant Java objects such as java.sql.Timestamp or java.time.OffsetDateTime when stored in TIMESTAMP columns.

    Note that it 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, the session is already set with the required time zone.

    See also 'connectionTimeZone' and 'preserveInstants' for more details.

    Default Value false
    Since Version 8.0.23
  • noDatetimeStringSync

    Don't ensure that ResultSet.getDatetimeType().toString().equals(ResultSet.getString())

    Default Value false
    Since Version 3.1.7
  • preserveInstants

    If enabled, 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 their original visual form. Otherwise, the driver 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.

    MySQL uses implied time zone conversion for TIMESTAMP values: they are converted from the session time zone to UTC for storage, and back from UTC to the session time zone for retrieval. So, to store the correct correct UTC value internally, the driver converts the value from the original time zone to the session time zone before sending to the server. On retrieval, Connector/J converts the received value from the session time zone to the JVM default one.

    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.

    Note that 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. Though, in this case, its still possible to store a correct instant value if set 'forceConnectionTimeZoneToSession=true'.

    See also 'connectionTimeZone' and 'forceConnectionTimeZoneToSession' for more details.

    Default Value true
    Since Version 8.0.23
  • sendFractionalSeconds

    If set to "false", the fractional seconds will always be truncated before sending any data to the server. This option applies only to prepared statements, callable statements or updatable result sets.

    Default Value true
    Since Version 5.1.37
  • sendFractionalSecondsForTime

    If set to "false", the fractional seconds of java.sql.Time will be ignored as required by JDBC specification. If set to "true", its value is rendered with fractional seconds allowing to store milliseconds into MySQL TIME column. This option applies only to prepared statements, callable statements or updatable result sets. It has no effect if sendFractionalSeconds=false.

    Default Value true
    Since Version 8.0.23
  • treatUtilDateAsTimestamp

    Should the driver treat java.util.Date as a TIMESTAMP for the purposes of PreparedStatement.setObject()?

    Default Value true
    Since Version 5.0.5
  • yearIsDateType

    Should the JDBC driver treat the MySQL type "YEAR" as a java.sql.Date, or as a SHORT?

    Default Value true
    Since Version 3.1.9
  • zeroDateTimeBehavior

    What should happen when the driver encounters DATETIME values that are composed entirely of zeros (used by MySQL to represent invalid dates)? Valid values are "EXCEPTION", "ROUND" and "CONVERT_TO_NULL".

    Default Value EXCEPTION
    Since Version 3.1.4