Retrieve the server's time zone

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Retrieve the server's time zone

Thomas Kellerer
Hello,

is there any way (short of writing a function in an untrusted PL) to determine the actual time zone (or time) of the server OS?

"show timezone" always returns the client's time zone.

localtimestamp also converts the server's time to the client time zone (the one defined by "timezone")

log_timezone is also unreliable as it can be changed to anything.

I am looking for something along the lines of: "show server_timezone" or "select current_timestamp at time zone server_timezone"

Is that possible?




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Retrieve the server's time zone

Tom Lane-2
Thomas Kellerer <[hidden email]> writes:
> is there any way (short of writing a function in an untrusted PL) to determine the actual time zone (or time) of the server OS?

The default value of the timezone parameter is as close as you'll get
in modern versions of PG.

> "show timezone" always returns the client's time zone.

AFAIK that would only be true if some part of your client stack
is issuing a SET TIMEZONE command.  (libpq will do that if it finds
a PGTZ environment variable set, but not in response to plain TZ.)

If that's true, and you can't/don't want to change it, you could try

select reset_val from pg_settings where name = 'TimeZone';

                        regards, tom lane


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Retrieve the server's time zone

Thomas Kellerer
Tom Lane schrieb am 14.11.2017 um 15:36:
>> is there any way (short of writing a function in an untrusted PL)
>> to determine the actual time zone (or time) of the server OS?
>
> AFAIK that would only be true if some part of your client stack
> is issuing a SET TIMEZONE command.  (libpq will do that if it finds
> a PGTZ environment variable set, but not in response to plain TZ.)

Ah, interesting. I do that through JDBC, so apparently that's the part to blame.
 
> If that's true, and you can't/don't want to change it, you could try
>
> select reset_val from pg_settings where name = 'TimeZone';

Hmm, this does not seem to work.

I am connected to a server with Asia/Bangkok but through JDBC
that query still returns Europe/Berlin (which is my client's time zone)

So apparently the JDBC driver somehow "persists" this setting.

I will take this to the JDBC mailing list then, thanks.

Thomas



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Previous Thread Next Thread