Mysql: SET time_zone = "+00:00" and pool

[vinnitu]

Hi.

I need UTC in my TIME queries, but I don't have permission on target machine to do global modification on time settings in mysql.ini

Trouble can me solved with SET time_zone = "+00:00", but only if I use mysql.createConnection() and exec SET query at beginning of all rest operations.

In my application I prefer use mysql.createPool and I don't known where I need to exec SET time_zone...

As I understand pool can recreate connection if it is was broken or timed out. And we need apply SET time_zone on every reconnect (connection)...

Please help me.

[dresende]

Doesn't this work for you?

var db = mysql.createPool("mysql://username:password@hostname/database?tz=UTC");

[vinnitu]

no :(

[dougwilson]

Hi @vinnitu , this is what you want to do for the pool:

var mysql = require('mysql');

var pool = mysql.createPool({
  host: 'localhost',
  timezone: '+00:00'
});

pool.on('connection', function onConnection(connection) {
  connection.query('SET time_zone = ?', '+00:00');
});

// Now just use the pool normally and every single connection
// you ever get from the pool will have SET time_zone ran on it

[vinnitu]

It works!

pool.on('connection', function onConnection(connection) {
  connection.query('SET time_zone = ?', '+00:00');
});

Thank You!

[dougwilson]

yay, you're welcome! :tada: