How to Resolve MySQL and Java Timezone Mismatches When `useTimezone=true` Fails?

Adjusting MySQL Timezone in Database Connections with Java

Problem Description:

MySQL is operating in "GMT 8" timezone, while Tomcat is using "GMT". This discrepancy leads to inconsistencies in datetime storage and retrieval when interacting with the database using Java. Despite setting "useTimezone=true" and "serverTimezone=GMT" in the connection URL, the issue persists.

Solution:

To resolve the timezone mismatch, the following approach is recommended:

1. Disable Legacy Date Time Code:

   Update the connection URL to include "useLegacyDatetimeCode=false". This will activate a newer and more accurate method for managing timestamps in MySQL.

   String url = "jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false";

2. Use Null Calendar for Timestamps:

   When setting timestamps using the setTimestamp method, ensure that no Calendar object is provided as the third parameter. This step will allow the date object to be formatted according to the database's timezone.

   getTimestamp(int, Timestamp)

3. Specify Server Timezone (Optional):

   If MySQL encounters ambiguity in determining the server timezone, it may be necessary to explicitly specify it in the connection URL.

   String url = "jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false&serverTimezone=America/New_York";

By implementing these measures, the timezone mismatch between MySQL and Java can be eliminated, ensuring correct datetime storage and retrieval in the database.

The above is the detailed content of How to Resolve MySQL and Java Timezone Mismatches When `useTimezone=true` Fails?. For more information, please follow other related articles on the PHP Chinese website!