Version Vertx 4.3.4, JDBCClient
Questions Our Oracle-database is setup in the local-timezone "Europe/Vienna" and returns the local-time over JDBC. But within the convertion of the VertX-JDBC the time markt in any case to UTC.
If you do a
pool.preparedQuery("select SESSIONTIMEZONE as sessionTimeZone, sysdate as dateValue, to_char(sysdate,'yyyy-dd-mm hh24:mi:ss') localTime from dual")
The output will be:
"sessionTimeZone":"Europe/Vienna", "dateValue":"2022-10-17T10:06:45Z", "localTime":"2022-17-10 10:06:45"
I assume it is related to this code-part (JDBCDecoderImpl.java, Line 188), where the returned timestamp is set hard-coded to UTC:
if (descriptor.jdbcType() == JDBCType.TIMESTAMP || descriptor.jdbcType() == JDBCType.TIMESTAMP_WITH_TIMEZONE) {
return LocalDateTime.parse(value.toString(), DateTimeFormatter.ISO_LOCAL_DATE_TIME).atOffset(ZoneOffset.UTC);
}
The oracle-jdbc-driver always return the timestamp in the timezone the database is setup. There is no possibility as it is at MySQL, where the timezone could be defined.
Is there a way to workaround this conversion?
This is a known issue from JDBC with Oracle Databases. In order to correctly set the timezone (without requiring any code changes) you can issue the following statement at start of each connection:
ALTER SESSION SET TIME_ZONE = 'UTC'
This will allow you to set the timezone for the session which should match the application. In this case, UTC
.
There are other options too. For example if you can modify the database you can set the database timezone at creation time:
CREATE DATABASE myDB
...
SET TIME_ZONE='UTC';
Or modify it after creation:
ALTER DATABASE myExistingDB
...
SET TIME_ZONE='UTC';
If you don't want/can alter the database and want to keep using the vert.x client without managing the connection yourself, in other words, keep using 1 shot queries, you can specify at the query level the timezone too:
SELECT
FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', '-04:00')
AT TIME ZONE '+12:00'
FROM DUAL;