Skip to content

Instantly share code, notes, and snippets.

@imtiazShakil

imtiazShakil/MySQL_Connector-J-8-0.md

Last active September 13, 2022 16:57
Show Gist options
  • Save imtiazShakil/790c7138ba566b416066696798be8105 to your computer and use it in GitHub Desktop.
Save imtiazShakil/790c7138ba566b416066696798be8105 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL Connector/J 8.0 DateTime Conversion Guide
Raw
MySQL_Connector-J-8-0.md

Question:
https://dev.mysql.com/doc/refman/8.0/en/datetime.html

According to doc, other than TIMESTAMP, date & datetime values are not converted for storage using any time zone. This makes since, since if one uses LocalDate & LocalDateTime in Java, which also doesn't contain any time zone info, and since MySQL says no timezone conversion happens for storing/retreiving date/datetime values, why should I define a timezone to MySQL connection to retrieve those values correctly?

Answer:
The way MySQL Server handles temporal data and temporal data exchanged between a Client and a Server are different things.

The documentation you referred pertains to how MySQL Server stores and retrieves temporal data and if some kind of TZ adjustments occur in the process. For example, suppose the server is configured with TZ UTC+02:00 and you store the same instant X in a TIMESTAMP column and a DATETIME column; when you retrieve the same data you'll see the same instant X values, but if you change the server TZ to UTC+03:00 and retrieve the same records, the TIMESTAMP value will have an instant X plus one hour ahead of the DATETIME.

So, this is only on server side. Between client and server a similar transformation occurs, except that Connector/J applies it to all temporal data types and not only TIMESTAMP. One of the reasons for this is because TIMESTAMP is quite limited in terms of range. So, if you have two clients in different time zones retrieving the same data you either expect to get them adjusted to their time zones or not and in both cases Connector/J needs to know exactly what's the time zone in the server.

Note: Connector/J 8.0 always performs datetime conversion. Read this reference
More reference

My Experiments:

I adjust spring.datasource.url=jdbc:mysql://localhost:3306/db?connectionTimeZone=? to check when time conversion is happening and when not.

test: 1
application timezone: utc
serverTimezone: utc, utc+2, utc+6
result = no conversion occurs

test: 2
application timezone: utc
connectionTimeZone: SERVER (internally +6)
result = conversion occurs

test: 3
application timezone: utc+6
connectionTimeZone: UTC
result = conversion occurs

test: 4
application timezone: utc
serverTimezone: Europe/Berlin ... Europe/Amsterdam ... America/New_York
result = conversion occurs

connectionTimeZone is equivalent to serverTimezone

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment