How to change MySQL timezone in a database connection using Java

Question

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 amp serverTimezone GMT   but it does not work

User · Answer

JDBC uses a so-called  connection URL   so you can escape     by   2B   that is  useTimezone true amp serverTimezone GMT 2B8

User · Answer

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

User · Answer

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 amp serverTimezone America New York     You only need to do this if it complains

User · Answer

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

[java] How to change MySQL timezone in a database connection using Java?

The answer is

Examples related to java

Examples related to mysql

Examples related to jdbc

Examples related to timezone

Tags