Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 355
Xilang Avatar asked Sep 30 '11 04:09

Xilang


People also ask

How do I set timezone in JDBC?

To set the timezone for a given JDBC connection, navigate to the Advanced tab and select the timezone from the dropdown menu. By default, UTC is selected.

How do I set the time zone in MySQL?

Option 2: Edit the MySQL Configuration File Scroll down to the [mysqld] section, and find the default-time-zone = "+00:00" line. Change the +00:00 value to the GMT value for the time zone you want. Save the file and exit. In the example below we set the MySQL Server time zone to +08:00 (GMT +8).

How do I populate MySQL database time zone?

The utility being used in the question is a script bundled with MySQL Server called mysql_tzinfo_to_sql . It reads your Linux (or FreeBSD, Solaris, or macOS) system's time zone database and creates SQL statements from the information it discovers, that will load the time zone tables in MySQL.


2 Answers

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.

like image 60
nogridbag Avatar answered Oct 06 '22 00:10

nogridbag


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

useTimezone=true&serverTimezone=GMT%2B8 
like image 23
Jay Avatar answered Oct 06 '22 01:10

Jay