Search code examples
javaoraclespring-bootojdbc

How to set serverTimeZone in Oracle JDBC database URL?

I'm getting an error when I try to start a spring-boot application with Oracle connection:

csm-security              | 2022-06-29 19:26:21.571  INFO [csm-security,,] 1 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2402 ms
csm-security              | 2022-06-29 19:26:22.329  INFO [csm-security,,] 1 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
csm-security              | 2022-06-29 19:26:23.847 ERROR [csm-security,,] 1 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.
...
csm-security              | java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
csm-security              | ORA-01882: timezone region not found

I've tried to put useTimezone and serverTimezone on my application.properties but it didn't work:

spring.datasource.url=jdbc:oracle:thin:@192.1..1.1:1521:desastg?useTimezone=true&serverTimezone=UTC

I've also tried to put this:

spring.datasource.hikari.data-source-properties.oracle.jdbc.timezoneAsRegion=true

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
Solution

  • My server timezone was:

    Command: timedatectl

    Time zone: America/Argentina/Buenos_Aires (-03, -0300)

    And in Oracle was:

    Sql:SELECT DBTIMEZONE FROM dual;

    +00:00

    So I have to change on application.properties to put timezoneAsRegion to false:

    spring.datasource.hikari.data-source-properties.oracle.jdbc.timezoneAsRegion=false

    According to Oracle documentation when timezoneAsRegion is set to true it uses default JVM timezone rather than convert to a GMT offset.