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

postgresql datetime timezone
Deutro picture Deutro · Jan 29, 2015 · Viewed 104.2k times · Source

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

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Jan 29, 2015

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

Related questions

Can't subtract offset-naive and offset-aware datetimes

I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age. The problem I'm having is that both datetime.datetime.…

Read More
PostgreSQL date() with timezone

I'm having an issue selecting dates properly from Postgres - they are being stored in UTC, but not converting with the Date() function properly. Converting the timestamp to a date gives me the wrong date if it's past 4pm PST. 2012…

Read More
Ignoring time zones altogether in Rails and PostgreSQL

I'm dealing with dates and times in Rails and Postgres and running into this issue: The database is in UTC. The user sets a time-zone of choice in the Rails app, but it's only to be used when getting the …

Read More