How do I get the current timezone name in Postgres 9 3

Question

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

User · Answer

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

User · Answer

It seems to work fine in Postgresql 9 5   SELECT current setting  TIMEZONE

User · Answer

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     gt     date   Z

User · Answer

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

User · Answer

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

[postgresql] How do I get the current timezone name in Postgres 9.3?

Examples related to postgresql

Examples related to datetime

Examples related to timezone