search

amphp / postgres

Async Postgres client for PHP based on Amp.
MIT License
97 stars 20 forks source link

Set default time zone for pool #42

Closed PNixx closed 1 year ago

PNixx commented 3 years ago

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 commented 3 years ago

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 commented 1 year ago

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.