3

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?

Andrus
  • 26,339
  • 60
  • 204
  • 378
  • Can you check if there is a difference in the Time Zones between these 2 versions? I believe this would be in the postgresql.conf file https://eureka.ykyuen.info/2014/08/15/postgresql-set-and-get-timezone/ – Igoranze Jul 21 '16 at 10:55
  • I updated question with timezone info. How to get server local time in 9.5 when default postgresql.conf file is used ? – Andrus Jul 21 '16 at 11:09
  • What do you mean with "When default postgresql.conf file is used"? Can't you change the timezone settings in postgresql.conf? – Igoranze Jul 21 '16 at 11:20
  • I'm looking for a way to create application which works with any postgresql.conf setting. `set timezone='gmt-3'; select current_time::char(8)` works but it uses hard coded timezone. How to replace `gmt-3` with server OS timezone ? – Andrus Jul 21 '16 at 11:28
  • This requirement is kind of weird. It is common to use the client time zone. Why the server one? Could you elaborate? – Clodoaldo Neto Jul 21 '16 at 12:14
  • This is for logging purposes. Time is stored in char(8) column in server log file. It should be server OS time. – Andrus Jul 21 '16 at 13:16

3 Answers3

4

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

If there is psql at the client:

psql -c "\! date +%Z" --host localhost --dbname=cpn --no-password
BRT

It is necessary a .pgpass file to avoid supplying the password.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • This uses hard coded time zone `brt` . Application uses servers from different time zones. How to use server OS time zone ? – Andrus Jul 21 '16 at 11:35
  • Application is running with non-superuser rights. How it can force server to get OS timezone ? – Andrus Jul 21 '16 at 12:06
  • @Andrus It is not necessary to be a superuser to get OS timezone. I issued the above shell calls as non superuser. – Clodoaldo Neto Jul 21 '16 at 12:16
  • Application can access server only using 5432 port. There is no SSH access to server, all other ports are closed. How to get server OS time in this case ? – Andrus Jul 21 '16 at 12:20
0

If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if not specified.) source

This means that if you do not define a timezone, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime().

If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone.

I believe you need to remove the # in #timezone = 'GMT' And replace GMT with '(defaults to server environment setting)' Because '(defaults to server environment setting)' is undefined and thus the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime().

Igoranze
  • 1,506
  • 13
  • 35
  • 1
    In 9.5 `The built-in default is GMT`, it will not try to determine it (https://www.postgresql.org/docs/9.5/static/runtime-config-client.html#GUC-TIMEZONE ) How to use server OS time always, without changing postgresql.conf file ? – Andrus Jul 21 '16 at 11:49
  • Each time you install a new postgresql server you are asked to set a timezone, if you don't pick one then (as you stated) the default is set (9.5: GMT). The Doc also states: *, but that is typically overridden in postgresql.conf*. So if you don't pick a timezone at the installation or change it later in the postgresql.conf, then there is no way to 'auto select ' the machines default – Igoranze Jul 21 '16 at 11:53
  • 1
    Timezone is not asked on server installation. Application has no control how postgres server is installed and has no rights to change postgresql.conf. It is running with non-superuser rights. How to get OS timezone in this case? – Andrus Jul 21 '16 at 12:08
  • Check permissions and owner for postgresql.conf file. Also make sure you edit right config file, run `sudo -u postgres /usr/local/pgsql/bin/psql` and enter `SHOW config_file;` Also check if the user you are using to change postgresql settings has sufficient rights. – Igoranze Jul 21 '16 at 12:12
  • Application can access to server only using 5432 port. There is no SSH access to server, all other ports are closed. How to get server OS time in this case ? – Andrus Jul 21 '16 at 12:20
  • In 9.2 the paragraph attached to this answer has disappeared from documentation https://www.postgresql.org/docs/9.2/datatype-datetime.html#DATATYPE-TIMEZONES – EAmez Dec 17 '19 at 14:27
-1

To obtain the local time calculated with the defined timezone:

select current_time, version();

To obtain the local time calculated with a specified timezone:

select current_time at time zone 'US/Eastern';

To obtain the defined timezone:

SELECT current_setting('TIMEZONE');

To assign the timezone that you want to use:

set timezone='US/Eastern';

You can see the different timezones here

Edu
  • 2,354
  • 5
  • 32
  • 36
adrian.ramirez
  • 11