137

I want to get the current timezone name. What I already achieved is to get the utc_offset / the timezone abbreviation via:

SELECT * FROM pg_timezone_names WHERE abbrev = current_setting('TIMEZONE')

This gives me all Continent / Capital combinations for this timezone but not the exact timezone. For example I get:

Europe/Amsterdam
Europe/Berlin

The server is in Berlin and I want to get the timezone name of the server.

The problem I have with CET that it is always UTC+01:00 and does not account for DST iirc.

Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
Deutro
  • 3,113
  • 4
  • 18
  • 26
  • The proper time zone name for either of Berlin and Amsterdam is Central European Time (CET). In general, time zone names and abbreviations are not well defined; while there is an ISO standard many countries use their own definitions. PostgreSQL support is also not complete, see http://www.postgresql.org/docs/9.4/static/datetime-config-files.html for some details. – Patrick Jan 29 '15 at 14:26
  • 1
    In the pg_timezone_names table CET is defined as abbreviation and e.g "Europe/Berlin" as name. I need the name and not the abbreviation. – Deutro Jan 29 '15 at 14:33
  • The link I gave in my previous comment shows you how you can edit the files to provide what you need. That is as good as it gets. – Patrick Jan 29 '15 at 14:37
  • So there is no way for postgres to tell me wether I am in "Europe/Berlin" or "Europe/Amsterdam" just that I am in the timezone CET? – Deutro Jan 29 '15 at 14:39
  • Can you edit your question and define "I am in"? A server is (usually) in a fixed location and the time zone is taken from the operating system or the configuration file. The tz name is as fixed as the server. So do you indeed want the time zone name of the server or of data in the database? – Patrick Jan 29 '15 at 14:46
  • Edited my question. Hope this helps you to understand what I really want to achieve. – Deutro Jan 29 '15 at 14:49
  • This isn't totally relevant to the original question, but a related command that's good to to know -- changing the database's timezone is simply `set timezone to 'UTC';` – Hartley Brody Oct 04 '18 at 18:50

5 Answers5

192

I don't think this is possible using PostgreSQL alone in the most general case. When you install PostgreSQL, you pick a time zone. I'm pretty sure the default is to use the operating system's timezone. That will usually be reflected in postgresql.conf as the value of the parameter "timezone". But the value ends up as "localtime". You can see this setting with the SQL statement.

show timezone;

But if you change the timezone in postgresql.conf to something like "Europe/Berlin", then show timezone; will return that value instead of "localtime".

So I think your solution will involve setting "timezone" in postgresql.conf to an explicit value rather than the default "localtime".

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
49

It seems to work fine in Postgresql 9.5:

SELECT current_setting('TIMEZONE');
KotGaf
  • 609
  • 5
  • 6
31

This may or may not help you address your problem, OP, but to get the timezone of the current server relative to UTC (UT1, technically), do:

SELECT EXTRACT(TIMEZONE FROM now())/3600.0;

The above works by extracting the UT1-relative offset in minutes, and then converting it to hours using the factor of 3600 secs/hour.

Example:

SET SESSION timezone TO 'Asia/Kabul';
SELECT EXTRACT(TIMEZONE FROM now())/3600.0;
-- output: 4.5 (as of the writing of this post)

(docs).

koyae
  • 720
  • 7
  • 12
  • 6
    Another user [suggested](http://stackoverflow.com/review/suggested-edits/12763260) using `SELECT EXTRACT(TIMEZONE_HOUR FROM now())`, but this is mildly dangerous because it **ignores** the fact that there are **both half and quarter timezones** out there. @giladMayani – koyae Jun 22 '16 at 17:57
  • 3
    _"North Korea, Newfoundland, India, Iran, Afghanistan, Venezuela, Burma, Sri Lanka, the Marquesas, as well as parts of Australia use half-hour deviations from standard time. Some nations, such as Nepal, and some provinces, such as the Chatham Islands, use quarter-hour deviations."_ ([link](https://en.wikipedia.org/wiki/Time_zone#Worldwide_time_zones)) – koyae Jun 22 '16 at 17:57
25

You can access the timezone by the following script:

SELECT * FROM pg_timezone_names WHERE name = current_setting('TIMEZONE');
  • current_setting('TIMEZONE') will give you Continent / Capital information of settings
  • pg_timezone_names The view pg_timezone_names provides a list of time zone names that are recognized by SET TIMEZONE, along with their associated abbreviations, UTC offsets, and daylight-savings status.
  • name column in a view (pg_timezone_names) is time zone name.

output will be :

name- Europe/Berlin, 
abbrev - CET, 
utc_offset- 01:00:00, 
is_dst- false
krishna
  • 1,029
  • 6
  • 12
Sachin R
  • 351
  • 3
  • 2
11

See this answer: Source

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.

It seems to have the System's timezone to be set is possible indeed.

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

=> \! date +%Z
Community
  • 1
  • 1
Igoranze
  • 1,506
  • 13
  • 35