If you do not feel anything special when someone says “timezones” then you can consider yourself to be lucky.
If your application runs only in your local time zone a you never ever need to convert date/time between timezones then you are really lucky.
But if you are right now crazy from time zone problems then maybe this text can help you.
- Global timezone for the whole database is set in postgresql.conf file:
timezone = 'UTC' # actually, defaults to TZ environment # setting
(warning – do not mistake it with “log_timezone” parameter which is first in postgresql.conf file.)
- In pg_settings view this parameter has name “TimeZone” – this is the only parameter in this view which does not have name in lower cases.
- Names of timezones and their offset to UTC in hours (as data type “interval”) you can find in pg_catalog view pg_timezone_names.
- You can set local timezone for session/ connection/ transaction this way:
set local time zone 'Europe/Berlin'; ..... your code .... reset timezone;
- If you get your timezone as offset you can also set it using this offset (in hours):
set local time zone 8;
Or “minus number” for America.
Be aware that there are timezones which has offset not in whole hours – for example in Australia they have offset like 9.5 hours or 8.75 hours.
But good news is that you can use directly this number to set time zone like: “set local time zone 8.75;”
So if you get offset from operating system in seconds you just need to divide it with 3600 as “numeric” or “real” and use this result.
- If you have this calculated offset in variable you cannot simply call “set local time zone ” with variable. This will end with error “invalid value for TimeZone”.
In that case you need to use EXECUTE:
execute 'set local time zone '||_offset_hours;
- Once you set this offset for session or transaction all date / time functions will give you result for this offset.
- If you need to get result for UTC or another timezone regardless of settings just use “AT TIME ZONE” like this:
select now() at time zone 'UTC';
- If you use “AT TIME ZONE” clause for timezone different from UTC you will get result as the type “TIMESTAMP WITH TIME ZONE”.
If you use UTC timezone then result will be of the type “TIMESTAMP WITHOUT TIME ZONE”.
Which means that you also need to use these data types in your application precisely in this meaning.
- Command “reset timezone;” will return timezone setting based on config file.
- Bad news is that so far (august 2015) there is no simple way in PostgreSQL to read timezone set on operating system. At least I did not find any. Therefore we use our ETL process to give us this information.