How to configure correct timezone in JDBC?

问题:

I've this url to set up the connection in my Italy website, however, when i try to perform some insert action from the site, the date is still not right. (it should be for example: 01:24, but it is 02:24)

jdbc.url=jdbc:mysql://sql.example.com/database?autoReconnect=true&characterEncoding=UTF-8&sessionVariables=time_zone='Europe/Rome'

Do I need to add any other params to make it work correctly? Is there a complete list of all timezones?

回答1:

Sorry I don't have the answer to your direct question. However I can suggest something worth considering that will avoid all time zone problems at the database entirely. If possible I recommend simply using BIGINT fields for storing dates with Java. You just store the long of the number of milliseconds since the epoch, e.g. from System.currentTimeMillis() or Date.getTime().

Then interpretation of the time zone for a date is always managed in Java, which is good at using the epoch based number. It does make it a little more involved to directly query the database for a date outside of Java, however it's not too hard and tends to be worth it IMO:

SELECT FROM_UNIXTIME(date_field / 1000) FROM table;

回答2:

There is a list of "tz" timezone names in Wikipedia.