How to set timezone for Postgres psql?

Question

How do I set timezone for psql to something other than my default (US/Central)? Here's what I've tried so far:

$ psql
psql (9.1.4, server 9.0.4)
...

$ psql -c 'show timezone'
  TimeZone  
------------
 US/Central

$ psql --set=timezone=US/Eastern -c 'show timezone'
  TimeZone  
------------
 US/Central

$ psql --variable=timezone=US/Eastern -c 'show timezone'
  TimeZone  
------------
 US/Central

Edit: I don't want to change the server timezone, just the client.

Edit #2: I want it in non-interactive mode.

Stichous

Changing the timezone should be done using the SQL statement set, not through psql commands (see Richard's answer) – Alienor

Answer 1

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)

Singlecross

Note you don't need to export it. At the bash prompt run: PGTZ=US/Eastern psql -c 'show timezone' – Stichous

Answer 2

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"

Agnail

Yes, I can do it in interactive mode, but I want non-interactive, i.e. to run with -c. – Stichous

Richard, It works for temporarily, when I close the SQL pane then it backs to the old one, can you provide permanent solution – Balcke

@chintan, this is really a separate question, but you can use "ALTER USER" or "ALTER DATABASE" to set configuration permanently. – Agnail

@ChintanPanchal Use ALTER DATABASE database_name SET TIMEZONE='zone'; then SET TIMEZONE='zone';. It seems to set it permanently for the specified database. – Ciccia

Answer 3

ALTER USER postgres SET timezone='Asia/Tokyo' ;

Shaquana

This worked for me with Amazon RDS with no restart required. Thx! – Perk

To change timezone for database: ALTER DATABASE database_name SET TIMEZONE='zone'; then SET TIMEZONE='zone';. – Ciccia

Answer 4

Note many third-party clients have own timezone settings overlapping any Postgres server and\or session settings.

E.g. if you're using 'IntelliJ IDEA 2017.3' (or DataGrips), you should define timezone as:

'DB source properties' -> 'Advanced' tab -> 'VM Options': -Duser.timezone=UTC+06:00

otherwise you will see 'UTC' despite of whatever you have set anywhere else.

Florencio