JDBC Oracle and Timestamps

[nikodemusP]

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):

        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 database returns "2022-17-10T10:06:45" and the atOffset(ZoneOffset.UTC) marks this time as UTC-Time. I looked at the JDBC-driver of Oracle and there is no way to make a time-convertion to UTC as it could be done for MySQL. Is there a way to workaround this convertion?

[zero88]

Hi @nikodemusP I think something is mismatched here. SESSIONTIMEZONE returns the time zone of the current connection session. the time zone is Vienna (UTC+2) sysdate is the current date and time set for the operating system on which the database server resides. As your output "dateValue":"2022-10-17T10:06:45Z", it is UTC+0.

So the dateValue and localTime is in tz UTC+0. Then I think the output is 2022-17-10T10:06:45 is correct (although JDBCDecoderImpl.java, Line 188 is not correct 100%, should switch case between TIMESTAMP and TIMESTAMP_WITH_TIMEZONE -- I already provided a fixed patch, might be available at 4.3.5).

Another thought, when using sql client pool without any specified configuration, the JDBC driver will use the timezone of your backend server/local system, not database timezone. For example:

• the actual db value is 2022-10-17T07:00:00+02:00, your backend timezone is +4
• then the JDBC driver output will be 2022-10-17T09:00:00+04:00
• Vertx sql client output is 2022-10-17T05:00:00+00:00

Back to your question, as default, the Vertx jdbc client always tries to return temporal value in ISO8601 with tz UTC+0. In case you want to customize it, just override JDBCDecoderImpl in the SPI file. Please follow document