Setting connection timezone with Spring and DBCP and MySQL?

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

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.

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

– Castanho May 11 '11 at 22:04 it's from the top of my head. I don't actually see a problem, but I haven't used it in production ;) – Bozho May 11 '11 at 22:06 I don't think it is a good idea to define some properties in your database in a multithreaded enviroment :S – Treydone May 11 '11 at 22:15 @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) – Bozho May 11 '11 at 22:25 @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. – Castanho May 11 '117 at 2:31.

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 Depending on your version of DBCP, you may have to use connectionInitSqls as the property name. This information is straight from DBCP configuration documentation.

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 JEE web server, use it to initialize a JNDI datasource ;).

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!

– Castanho May 11 '11 at 22:01.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.