Get time format in PostgreSQL

Question

If I want get Ubuntu system time, I have two options:

$ cat /etc/timezone
US/Eastern
$ date
Sun Sep 15 14:45:02 EDT 2013

How can I find out if PostgreSQL is using utc or a utc offset, such as US/Eastern?

Answer 1

You can use current_setting() function to get the TIMEZONE setting.

SELECT current_setting('TIMEZONE') TZ;

Sample output:

# SELECT current_setting('TIMEZONE') TZ;
 tz 
------------
 US/Eastern
(1 row)

Here is SQLFiddle demo

Answer 2

To see all relevant settings for local time (and some irrelevant, too), you can use:

SELECT * FROM pg_settings
WHERE name ~* 'time'

In particular, the settings for TimeZone and lc_time should be of interest to you. (The time zone is not the only relevant detail for time format.) You get a short description in the column short_desc.) More details for these settings in the manual here.

If you are only interested in one particular setting, the shortest form is:

SHOW timezone;

Returns the same as SELECT current_setting('timezone') (like peterm provided), but the latter can also be integrated into DML SQL queries.