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.
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) |  |
|
|
| MySQL (MariaDB driver*) |  System timezoneneeds to be database timezone |
|
|
| MSSQL (official driver*) | |
|
|
| MSSQL (jtds driver*) | () Turns into datetime |
() Turns into datetime |
|
| Netezza (official driver) | Write: System timezone needs to be database timezone |
Write: System timezone needs to be database timezone |
Write: System timezone needs to be database timezone |
| Oracle (official driver*) | Write needs to be UTC |
|
|
| PostgreSQL (official driver*) | |
Write/read with same timezone |
Write/read with same timezone |
| Vertica (official driver) | |
|
|
* Shipped with Altair AI Studio