Setting session timezone with spring jdbc oracle

Question

I have a spring/jdbc/oracle 10g application. The Oracle server database timezone is set to GMT + 2 JVM timezone is GMT + 2 (even though it doesn't matter in my case).

I have a stored procedure that performs some date operations. The problem is that session timezone is different(GMT) than database timezone even though I do not set session timezone explicit in my code/configuration.

As far as I know the session timezone is by default equal to database timezone. Any idea why is the session timezone different than database timezone or how can I configure it in spring configuration (org.apache.commons.dbcp.BasicDataSource) ?

Thank you.

Which session do you mean? Oracle connection session, or HTTP session? The former, I assume? — skaffman, May 10 '10 at 20:24
This solve your problem? I need to do the same on MySQL and I'm still looking for how to do it! — rafa.ferreira, May 11 '11 at 20:40
I have questions/posts that stay in this DEAD mode!! No answers, no confirmations, no speculations!! — rafa.ferreira, May 12 '11 at 14:31

score 6 · Accepted Answer · answered May 13 '10 at 15:00

The correct way is to use DelegatingDataSource, retrieve OracleConnection object from the original data source and call OracleConnection.setSessionTimeZone() with the appropriate parameter.

C3P0 code looks like:

private Object[] timeZoneArgs = new Object[] { "Europe/Berlin" };

@Override
public Connection getConnection() throws SQLException {
    Connection conn = super.getConnection();
    try {
        final Method setSessionTimeZoneMethod = OracleConnection.class.getMethod("setSessionTimeZone", String.class);
        final C3P0ProxyConnection castCon = (C3P0ProxyConnection) conn;
        castCon.rawConnectionOperation(setSessionTimeZoneMethod, C3P0ProxyConnection.RAW_CONNECTION, timeZoneArgs);
        return conn;
    } catch (Exception e) {
        log.error("setSessionTimeZone failed " + e.getMessage());
        return conn;
    }
}

can be simplified: `conn.unwrap(OracleConnection.class).setSessionTimeZone("UTC");` — D-rk, Jan 30 '23 at 11:42

score 1 · Answer 2 · answered Aug 31 '12 at 08:21

1

I solved this problem by upgrading Oracle's JDBC drivers from v10.2.0.1.0 to v11.2.0.3. No changes to my java code were required.

Source: Spring forums.

answered Aug 31 '12 at 08:21

Grilse

3,491
2
28
35

score 1 · Answer 3 · edited Aug 16 '18 at 14:13

1

In spring configuration, add below VM options before running the application:

-Duser.timezone=EDT

Also make sure your pom has latest jdbc driver

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

edited Aug 16 '18 at 14:13

anothernode

5,100
13
43
62

answered Aug 16 '18 at 13:50

Abhimanyu Singh

11
2

Setting session timezone with spring jdbc oracle

3 Answers3

Linked