In Postgresql:

SHOW TIME ZONE;

... produces a result like Europe/Zurich or America/New_York according to the server time zone. Is there any equivalent command to show the client time zone (or perhaps client session time zone) in the same format, so that it can be passed to things like the timezone() function?

I have a server in Pacific/Auckland zone being passed records with TIMESTAMP values from a Australia/Sydney site, and I need that latter time zone so I can convert in certain circumstances using timezone( local_client_session_timezone, time_from_server ). So far I can only specify the value of local_client_session_timezone in that context from an external source. How do I get it from my database session?

CodePudding user response：

The timezone parameter is initialized from the server setting, but if you are writing a time zone aware application, each client session should override the value with SET to determine the session time zone.

Then you don't have to explicitly convert timestamps: simply use the data type timestamp with time zone. Every session inserts timestamps in its local time zone, and the timestamp is automatically converted to the session time zone of any client that queries the data. So everybody sees the same timestamp in their local time zone.

CodePudding user response：

If you set PGTZ environment variable, then libpq-based clients like psql, psycopg2 and DBD::Pg will automatically send that content over as the timezone in the start up packet. Other connection libraries will have other practices, which will hopefully be documented.