Specifying Timezone During Connection

JDBC for MySQL - Herong's Tutorial Examples

∟Specifying Timezone During Connection

This section describes how to specify serverTimezone property in the connection URL to avoid timezone exception and timezone conversion issue.

If you are not specifying the "serverTimezone" property in the connection URL, the JDBC driver will take the MySQL server's timezone, and use it as the default timezone for your SQL statements. This will lead to 2 potential issues:

Connection exception - If the server uses a timezone code that is not recognizable by the Java standard.
Incorrect timezone conversion - If the server's timezone is different than your local JVM environment.

So it is strongly recommended to specify the "serverTimezone" property in the connection URL. The "serverTimezone" property value should be specified as a Java timezone ID value as shown below:

// Explicitly as a Java timezone ID string
  String url = "jdbc:mysql:///?...&serverTimezone=Asia/Tokyo";

// Implicitly as the current JVM timezone via java.util.TimeZone API
  String tzid = java.util.TimeZone.getDefault().getID();
  String url = "jdbc:mysql:///?...&serverTimezone="+tzid;

// Implicitly as the current JVM timezone via java.time.ZoneId API
  tzid = java.time.ZoneId.systemDefault().getId();
  String url = "jdbc:mysql:///?...&serverTimezone="+tzid;

Here is an example program that connects to the local MySQL server without specifying the serverTimezone property.

/* ConnectionUrlTimezone.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class ConnectionUrlTimezone {
  public static void main(String [] args) {
    String mode = "None";
    String zone = "";
    String tzid = "";
    if (args.length>0) mode = args[0];
    if (mode.equals("None")) {
      zone = "";
    } else if (mode.equals("TimeZone")) {
      tzid = java.util.TimeZone.getDefault().getID();
      zone = "&serverTimezone="+tzid;
    } else if (mode.equals("ZoneId")) {
      tzid = java.time.ZoneId.systemDefault().getId();
      zone = "&serverTimezone="+tzid;
    } else if (mode.equals("Explicit")) {
      zone = "&serverTimezone=Asia/Tokyo";
      System.out.println("zone: "+zone);
    } else {
      System.out.println("Invalid mode: "+mode);
    }

    Connection con = null;
    try {
      String url = "jdbc:mysql://localhost/?"
        + "user=Herong&password=TopSecret"+zone;
      System.out.println("Connection URL: "+url);
      con = DriverManager.getConnection(url);
      System.out.println("Connected: "+con.getMetaData().getURL());
      con.close();

    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

If you run it without serverTimezone specified on a Linux system, you may get a timezone exception:

herong> javac -cp .:mysql-connector-java.jar ConnectionUrlTimezone.java
herong> java -cp .:mysql-connector-java.jar ConnectionUrlTimezone

Connection URL: jdbc:mysql://localhost/?user=Herong&password=TopSecret

Exception: The server time zone value 'EDT' is unrecognized 
or represents more than one time zone. You must configure either 
the server or JDBC driver (via the 'serverTimezone' configuration 
property) to use a more specific time zone value if you want 
to utilize time zone support.

The above error tells us that the MySQL server timezone is set to "EDT (Eastern Daylight Time)", which is not recognizable by Java. If you look at the server settings, you will see that the server timezone is set to SYSTEM, and the (operating) system timezone is set to "EDT".

herong> mysql -u root -p

mysql> show global variables like '%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | EDT    |
| time_zone        | SYSTEM |
+------------------+--------+

There are 2 options to resolve the problem as suggested in the error message.

1. Update the MySQL server timezone to the corresponding UTC offset value. But this may impact other applications running on the same server.

mysql> set global time_zone = '-05:00';

2. Add "serverTimezone" property in the connection URL as recommended above.

Try option 2 with the same example program. The timezone exception will go away.

herong> java -cp .:mysql-connector-java.jar ConnectionUrlTimezone Explicit
Connection URL: jdbc:mysql://localhost/?user=Herong&password=TopSecret
  &serverTimezone=Asia/Tokyo
Connected: jdbc:mysql://localhost/?user=Herong&password=TopSecret
  &serverTimezone=Asia/Tokyo

herong> java -cp .:mysql-connector-java.jar ConnectionUrlTimezone ZoneId
Connection URL: jdbc:mysql://localhost/?user=Herong&password=TopSecret
  &serverTimezone=America/New_York
Connected: jdbc:mysql://localhost/?user=Herong&password=TopSecret
  &serverTimezone=America/New_York