54

MySQL runs with timezone "GMT+8", but Tomcat with "GMT". When I save datetime to my database, everything seems to be OK, but when I check the datetime value in the database, I see the "GMT" value.

Also when I try get the value from the database the value is changed, seems like the value in the database is taken as "GMT+8", so Java changes the value to "GMT".

I have set the connection URL like this:

useTimezone=true&serverTimezone=GMT

but it does not work.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Xilang
  • 1,513
  • 3
  • 18
  • 36

6 Answers6

92

useTimezone is an older workaround. MySQL team rewrote the setTimestamp/getTimestamp code fairly recently, but it will only be enabled if you set the connection parameter useLegacyDatetimeCode=false and you're using the latest version of mysql JDBC connector. So for example:

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

If you download the mysql-connector source code and look at setTimestamp, it's very easy to see what's happening:

If use legacy date time code = false, newSetTimestampInternal(...) is called. Then, if the Calendar passed to newSetTimestampInternal is NULL, your date object is formatted in the database's time zone:

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

It's very important that Calendar is null - so make sure you're using:

setTimestamp(int,Timestamp).

... NOT setTimestamp(int,Timestamp,Calendar).

It should be obvious now how this works. If you construct a date: January 5, 2011 3:00 AM in America/Los_Angeles (or whatever time zone you want) using java.util.Calendar and call setTimestamp(1, myDate), then it will take your date, use SimpleDateFormat to format it in the database time zone. So if your DB is in America/New_York, it will construct the String '2011-01-05 6:00:00' to be inserted (since NY is ahead of LA by 3 hours).

To retrieve the date, use getTimestamp(int) (without the Calendar). Once again it will use the database time zone to build a date.

Note: The webserver time zone is completely irrelevant now! If you don't set useLegacyDatetimecode to false, the webserver time zone is used for formatting - adding lots of confusion.

Note:

It's possible MySQL my complain that the server time zone is ambiguous. For example, if your database is set to use EST, there might be several possible EST time zones in Java, so you can clarify this for mysql-connector by telling it exactly what the database time zone is:

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

You only need to do this if it complains.

nogridbag
  • 3,521
  • 4
  • 38
  • 51
  • Thank you for the useLegacyDatetimeCode suggestion! Note: This also solved my problems working with Joda Time (via the usertypes hibernate add-on) as well. – Pete Dec 10 '12 at 22:19
  • So with serverTimezone and useLegacyDatetimeCode added to my connection string, I can send a Timestmp and it gets recorded correctly in a DateTime column as it's UTC time. I can see it on my MySQL console as such. When I do a SELECT via JDBC and try to get the data out, it still automatically converts it in the JDBC Layer. I've tried getObject() and geTimestmp (both without and with a UTC calendar object) and it keeps coming back in local time. – djsumdog May 04 '14 at 14:31
  • Turns out this was the setting I needed to solve a production problem. Locally we could not reproduce it. But trying this in the driver on production eliminated the stall we were seeing after 10-30 seconds of processing. – Robert Casto Mar 21 '16 at 17:00
  • @djsumdog - Timezone info is not stored in the Timestamp class. So when you print a date object to the console it will be formatted in the JVM time zone. – nogridbag May 04 '16 at 18:58
  • 1
    try ```String url = "jdbc:mysql://localhost?tz=useLegacyDatetimeCode=false&serverTimezone=America/New_York";```. For my application this sample link works: ```url: jdbc:mysql://localhost:3306/trip_planner?tz=useLegacyDatetimeCode=false&serverTimezone=Europe/Berlin``` – mzober Jun 27 '16 at 09:51
  • Sorry for the confusion - tz in my example was the name of the database in the JDBC connection string. I edited my post and renamed it to mydb for clarity. – nogridbag Jun 28 '16 at 02:38
  • I like how you use "If you download the source code and look at the method, it's very easy to see what's happening" and "it should be obvious now how this works"... – sumek Aug 02 '16 at 13:40
  • [DEBUG] 2017-09-20 12:27:55.066 [http-nio-8182-exec-1] [12709eqId] SqlExceptionHelper logExceptions [1] [RELOAD] - Unable to acquire JDBC Connection [n/a] java.sql.SQLException: Cannot create PoolableConnectionFactory (Cannot load connection class because of underlying exception: com.mysql.cj.core.exceptions.WrongArgumentException: Malformed database URL, failed to parse the connection string near '=America/New_York;'.) – user2478236 Sep 20 '17 at 12:29
  • Can you post your complete JDBC connection URL? It might be the trailing semicolon or previous parameters. – nogridbag Sep 20 '17 at 19:08
  • String url = "jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false&serverTimezone=America/New_York"; **Worked For Me** – Shekh Firoz Alam Apr 12 '19 at 23:45
  • Thanks, ...?useLegacyDatetimeCode=false&serverTimezone=UTC solved my problem! – Marco S. Apr 30 '19 at 07:23
14

JDBC uses a so-called "connection URL", so you can escape "+" by "%2B", that is

useTimezone=true&serverTimezone=GMT%2B8
Jay
  • 326
  • 2
  • 8
  • Currently mysql-connector-java 5.1.33-37 complaint is caused always by a bug, because in most cases serverTimeZone parameter shouldn't be necessary. Refer this post: http://stackoverflow.com/questions/26515700/mysql-jdbc-driver-5-1-33-time-zone-issue/33893008#33893008 – Aníbal Sep 19 '22 at 21:34
1

For applications such as Squirrel SQL Client (http://squirrel-sql.sourceforge.net/) version 4 you can set "serverTimezone" under "driver properties" to GMT+1 (example of timezone "Europe/Vienna).

mtjmohr
  • 99
  • 9
0

Is there a way we can get the list of supported timeZone from MySQL ? ex - serverTimezone=America/New_York. That can solve many such issue. I believe every time you need to specify the correct time zone from the Application irrespective of the DB TimeZone.

Arindam
  • 675
  • 8
  • 15
  • You can use TimeZone.getAvailableIDs() method. basically returns a String array. Which you can printout and check the list of available timezones. – ThivankaW Jun 17 '21 at 08:57
0

Works for me

spring.datasource.url= jdbc:mysql://localhost:3306/catalog?serverTimezone=UTC

Maninder
  • 1,539
  • 1
  • 10
  • 12
0

Change databasename, username and userpassword

 connection = DriverManager.getConnection("jdbc:mysql://localhost/databasename?user=username&password=userpassword"
                                    + "&userUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetime=false&serverTimezone=UTC");
ipastorl
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – lemon May 17 '22 at 18:24