Re: [GENERAL] 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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Retrieve the server's time zone

[Tom Lane]

Thomas Kellerer  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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general