Categories

Versions

Setting the timezone for a JDBC connection

When creating a JDBC connection, you should also set the database timezone. This takes care of converting/interpreting date, datetime and time columns according to the database, so that no matter in which timezone you are yourself, you can collaborate without accidentally introducing a local timezone offset in your data.

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

../img/jdbc_timezone_handling.png

By default, UTC is selected. Special timezones are SYSTEM (to use the system timezone of the machine where the connection is used) and Don't set (to not explicitly set the timezone and let the JDBC driver itself manage it). JDBC connections created prior to 9.6 will have the value set to Don't set to keep compatibility.

Note: It is not recommended to use the Don't set setting, since the behavior will not be consistent between different machines (or maybe even runs on the same machine). The setting purely exists for compatibility reasons.

Unfortunately, some databases / JDBC drivers don't fare too well in regards to date handling, so please refer to the documentation of your database vendor for more information. The table below gives an overview of our tests against popular databases and their behavior at the time of writing.

SQL System Date Time Datetime
MySQL (offical driver) Always works Always works Always works
MySQL (MariaDB driver*) System timezone works System timezone
needs to be
database timezone
Always works Always works
MSSQL (official driver*) Always works Always works Always works
MSSQL (jtds driver*) (Always works)
Turns into datetime
(Always works)
Turns into datetime
Always works
Netezza (official driver) System timezone works Write:
System timezone
needs to be
database timezone
System timezone works Write:
System timezone
needs to be
database timezone
System timezone works Write:
System timezone
needs to be
database timezone
Oracle (official driver*) Write needs
to be UTC
Always works Always works
PostgreSQL (official driver*) Always works Write/read with
same timezone
Write/read with
same timezone
Vertica (official driver) Always works Always works Always works

* Shipped with Altair AI Studio