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.

Answer 1

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".