Set default time zone for pool

[PNixx]

Sql query: SET TIMEZONE TO 'UTC' not working, I receiving time in my local TZ 2021-03-22 14:57:49.834961+03.
But it work with connect psql:

# select now();
              now
-------------------------------
 2021-03-22 15:02:39.242053+03
(1 row)
# SET TIMEZONE TO 'UTC';
SET
# select now();
              now
-------------------------------
 2021-03-22 12:03:10.843069+00
(1 row)

[trowski]

The issue here it seems is that the connection returned from the pool may be a new connection instance. I can reproduce this with both ext-pgsql and ext-pq, as using SET TIMEZONE TO 'UTC' sets it only for that session, not any subsequent sessions.

This could be done for each connection returned from the pool. There's no way to do this currently, Pool would need a mechanism to specify a set of queries to run on each new connection.

As a workaround, you can use transactions, as that will allow you to perform a series of statements that is guaranteed to use the same connection. Otherwise of course you can of set the timezone on the server.

[trowski]

Revisiting this issue while preparing for the v2.0 release and realized I was incorrect in my last comment, this is currently possible in both 1.x and 2.x. Pass a custom implementation of PostgresConnector (or Connector in v1.x) instance to the pool object, which may then execute queries on the new connection before it is returned to the pool.

As no code changes need to be made, I'm closing this issue. If you have any questions on creating a custom connector, please open a new issue or reach out in chat.