How to set timezone for Postgres psql?
psql (9.1.4)
Type "help" for help.
richardh=> show timezone;
TimeZone
----------
GB
(1 row)
richardh=> set timezone='UTC';
SET
richardh=> show timezone;
TimeZone
----------
UTC
(1 row)
richardh=> set timezone='US/Eastern';
SET
richardh=> show timezone;
TimeZone
------------
US/Eastern
(1 row)
richardh=> set timezone='blah';
ERROR: invalid value for parameter "TimeZone": "blah"
The psql doc says:
-v assignment
--set=assignment
--variable=assignment
Perform a variable assignment, like the \set internal command. Note that
you must separate name and value, if any, by an equal sign on the command line....
But with the timezone it does not seem to work, perhaps because because of this:
These assignments are done during a very early stage of start-up,
so variables reserved for internal purposes might get overwritten later.
So, it seems you must either use the SET command inside psql, or either set the PGTZ environment variable:
PGTZ=PST8PDT psql -c 'show timezone'
Of course, if you are OK with setting the timezone globally for the user (not just for this individual psql instance), you might set that variable in its .bashrc
file (if in Linux)
ALTER USER postgres SET timezone='Asia/Tokyo' ;