How Can I Correctly Handle Timezones When Connecting Java to a MySQL Database?

Changing MySQL Timezone in Java Database Connections

MySQL operates with the "GMT 8" timezone, while Tomcat employs "GMT." When saving datetime to the database, values may seem correct, but when retrieved, the "GMT" values appear. Also, values retrieved from the database are converted to "GMT," suggesting the database considers them "GMT 8."

Solution

The useTimezone parameter is an outdated workaround. For a modern solution, set useLegacyDatetimeCode=false and upgrade to the latest mysql JDBC connector. An example connection URL:

jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false

Timestamp Handling

If useLegacyDatetimeCode is set to false, the newSetTimestampInternal() method is invoked. If the provided Calendar is null, the date object is formatted in the database's timezone:

this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
this.tsdf.setTimeZone(this.connection.getServerTimezoneTZ());
timestampString = this.tsdf.format(x);

To retrieve the date, use getTimestamp(int) without the Calendar. Again, the database timezone will be used to build the date.

Webserver Timezone Irrelevance

The webserver's timezone is now irrelevant for formatting. If useLegacyDatetimecode remains true, the webserver's timezone is used, leading to confusion.

MySQL Timezone Ambiguity

MySQL may complain about server timezone ambiguity when set to EST, for example. To resolve this, specify the exact EST timezone in the connection URL:

jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false&serverTimezone=America/New_York

This is only necessary if MySQL raises the ambiguity issue.

The above is the detailed content of How Can I Correctly Handle Timezones When Connecting Java to a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!