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.

Changing the timezone should be done using the SQL statement set, not through psql commands (see Richard's answer) — user330315
– user330315, Commented Aug 2, 2012 at 14:21

Richard Huxton · Accepted Answer · 2012-08-02 14:20:04Z

34

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"

answered Aug 2, 2012 at 14:20

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dfrankow Over a year ago

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

Chintan Panchal Over a year ago

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

Richard Huxton Over a year ago

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

leetbacoon Over a year ago

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

Abhilash Mishra · Accepted Answer · 2019-10-07 08:58:43Z

24

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

edited Oct 7, 2019 at 8:58

answered May 20, 2016 at 6:19

Abhilash Mishra

4634 silver badges13 bronze badges

2 Comments

Ryan Charmley Over a year ago

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

leetbacoon Over a year ago

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

leonbloy · Accepted Answer · 2014-10-02 13:18:12Z

20

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)

edited Oct 2, 2014 at 13:18

answered Aug 2, 2012 at 14:29

leonbloy

76.4k22 gold badges149 silver badges197 bronze badges

1 Comment

dfrankow Over a year ago

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

ARA1307 · Accepted Answer · 2018-01-09 18:53:22Z

5

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.

answered Jan 9, 2018 at 18:53

ARA1307

1,14711 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to set timezone for Postgres psql?

4 Answers 4

4 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related