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

$ cat /etc/timezone

$ 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?


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

SELECT current_setting('TIMEZONE') TZ;

Sample output:

# SELECT  current_setting('TIMEZONE') TZ;
(1 row)

Here is SQLFiddle demo


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.