Setting connection timezone with Spring and DBCP and MySQL

Question

My Enviroment

Java 5
Spring 2.5.5
DBCP DataSource (org.apache.commons.dbcp.BasicDataSource)
MySQL

Similar posts

Setting session timezone with spring jdbc oracle

Links

http://www.mysqlfaqs.net/mysql-faqs/General-Questions/How-to-manage-Time-Zone-in-MySQL

My Problem

I need to set on my connection the timezone, aiming to prevent the conversions when dealing with TIMESTAMP columns.

My Idea/research

DBCP Connection Pool did not mention anything around timezone. LINK
What I investigate and thought that was oK is described on THIS post, exemplifying is:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="URL" value="${database.url}" /> 
    <property name="user" value="${database.username}" /> 
    <property name="password" value="${database.passwd}" /> 
    <property name="connectionCachingEnabled" value="true"/>
    <property name="sessionTimeZone" value="GMT-3"/>
</bean>

Asking for help area :)

But this is not working!!
What I want here is a simple way, preferentially using Spring to configure the timezone on jdbc connection.

Thanks in advance for any help/tips/advice/knowledge share

SOLUTION:

My Solution was based on tips collected on this post! Thanks for all!

(...)
@Override
public Connection getConnection() {
    Connection conn = null;
    Statement statement = null;
    try {
        conn = super.getConnection();
        statement = conn.createStatement();
        statement.execute("SET time_zone = \'" + timezone+"\'");
    } catch (SQLException e) {
        LOG.fatal("Error while SET time_zone", e);
    } finally {
        try {
            statement.close();
        } catch (SQLException e) {
            LOG.warn("Error while closing statement", e);
        }
    }
    if(LOG.isDebugEnabled())
        LOG.debug("SET time_zone("+timezone+") for connection, succeed!");
    return conn;
}
(...)

and on my Spring configuration file:

<bean id="dataSource" class="com.my.package.dbcp.TimezoneEnabledDataSource" destroy-method="close">
    (...)
    <property name="timezone" value="${database.timezone}" />
    (...)
</bean>

I hope this post can help someone in the future. Any question ping me!

are you certain that GMT-3 is recognized? Have you tried America/Los_Angeles for example (for the sake of testing) — Bozho
– Bozho, Commented May 11, 2011 at 21:50
I'll keep updating my post with all my finds! I've just quit about find something regarding DBCP configurations! — rafa.ferreira
– rafa.ferreira, Commented May 11, 2011 at 21:50
@Bozho, I'll double check in some hours that I'm not @ home but as peer the link I mention the error is complaining about the property( Invalid property 'sessionTimeZone'), not the value for it. — rafa.ferreira
– rafa.ferreira, Commented May 11, 2011 at 21:52

Chandranshu · Accepted Answer · 2011-06-08 12:15:55Z

7

You should be able to put the same SQL statements in the initConnectionSqls property of the DBCP configuration element. Just add this to the DBCP configuration element

<property name="initConnectionSqls" value="SET time_zone = '${database.timezone}'"/>

Depending on your version of DBCP, you may have to use connectionInitSqls as the property name. This information is straight from DBCP configuration documentation.

answered Jun 8, 2011 at 12:15

Chandranshu

3,6693 gold badges22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LMC Over a year ago

For Oracle, the query could be: alter session set time_zone= ''${database.timezone}

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

If the data source doesn't have such a property, you can extend it and add that property:

public TimezoneEnabledDataSource extends BasicDataSource {
    private String timezone;
    //getter and setter for it

    @Override    
    public Connection getConnection() {
        Connection c = super.getConnection();
        // execute a query: SET time_zone = '-8:00'
        return c;
    }
}

See here http://www.electrictoolbox.com/mysql-set-timezone-per-connection/ for the query details.

MySQL documentation writes:

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

You can also check if c3p0 doesn't have something built-in.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 11, 2011 at 21:56

Bozho

599k147 gold badges1.1k silver badges1.2k bronze badges

13 Comments

rafa.ferreira Over a year ago

@Bozho, this looks like a solution!!! I'll focus some time on this! Did you see any problem by doing this kind of solution!?

Bozho Over a year ago

it's from the top of my head. I don't actually see a problem, but I haven't used it in production ;)

Vincent Devillers Over a year ago

I don't think it is a good idea to define some properties in your database in a multithreaded enviroment :S

Bozho Over a year ago

@Treydone I didn't understand your comment. I think the query sets the timezone per connection, not globally (at least that's what the article claims)

rafa.ferreira Over a year ago

@Treydone what are your concerns about it? I'm asserting that when works with timestamp columns, MySQL will not change my date due to different timezones. Thinking that MySQL will always be started at UTC.

|

Julia Shevchuk · Accepted Answer · 2018-08-09 15:20:23Z

0

One of the possible solutions:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="URL" value="${database.url}?serverTimezone=America/Los_Angeles" /> 
    <property name="user" value="${database.username}" /> 
    <property name="password" value="${database.passwd}" /> 
    <property name="connectionCachingEnabled" value="true"/>
    <property name="sessionTimeZone" value="GMT-3"/>
</bean>

answered Aug 9, 2018 at 15:20

Julia Shevchuk

1

Comments

Arjan Tijms · Accepted Answer · 2013-04-21 09:21:40Z

-1

There is no "sessionTimeZone" member in the BasicDataSource. Use C3P0 which is a "better" connection pool than DBCP, or even better, if you are in a Java EE web server, use it to initialize a JNDI datasource ;)

edited Apr 21, 2013 at 9:21

Arjan Tijms

38.2k12 gold badges112 silver badges144 bronze badges

answered May 11, 2011 at 21:57

Vincent Devillers

1,6281 gold badge11 silver badges17 bronze badges

2 Comments

rafa.ferreira Over a year ago

Hi Treydone, thanks for your help. I saw somewhere that is possible to configure that using C3P0, but I don't know if is a option, keeping in mind that is a huge system very solid. But I'll look for your solution!

Nicholi Over a year ago

Interestingly enough there is no sessionTimeZone or equivalent member in C3P0 DataSources either...

Collectives™ on Stack Overflow

Setting connection timezone with Spring and DBCP and MySQL

4 Answers 4

1 Comment

13 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

13 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related