Andrus Andrus - 4 months ago 22
SQL Question

How to get server OS local time in all Postgres versions

How to get server local time in char(8) format hh:mm:ss in every Postgres version.

In 9.1 it works:

select current_time::char(8)


returns proper local time 13:46:00

In 9.5 it returns 3 hour different time:

10:46:00

select current_time, version() returns

"10:48:40.181735+00";"PostgreSQL 9.5.2, compiled by Visual C++ build 1800, 32-bit"


and

"13:48:51.775138+03";"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit"


Update

Both servers use default postgres.conf settings for time.
postgres.conf does not contain timezone settings.

in 9.5 Windows 10 it contains

#timezone = 'GMT'
#timezone_abbreviations = 'Default'


in 9.1 Debian it contains

#timezone = '(defaults to server environment setting)'
#timezone_abbreviations = 'Default'


How to get server local time in 9.5 when default postgresql.conf file is used ?

It looks like server does not use OS setting in 9.5

How to force 9.5 to ask timezone from OS and return time in this zone?

Answer

Ask for the time zone you want:

select current_time at time zone 'brt';
      timezone      
--------------------
 08:26:16.778448-03

If you need a string:

select to_char(current_timestamp at time zone 'brt', 'HH24:MI:SS');
 to_char  
----------
 08:32:07

Notice that the to_char function does not accept the time type. Use timestamp instead.

Get the OS local time zone from the shell. In Linux:

$ date +%Z
BRT

In psql:

=> \! date +%Z
BRT
Comments