2

I have a time zone in PostgreSQL created like this:

ALTER DATABASE mydatabase SET TimeZone = 'MST';

I would like to use the preset time zone in restored database as well. If I backup it via pg_dump and restore with pg_restore the pg_settings/TimeZone is set back to localtime.

How can I transfer timezone?

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Michal Špondr
  • 1,337
  • 2
  • 21
  • 44
  • 1
    Your question is valid and this is not the answer but anyway: your application should never depend on the default PG server timezone. – leonbloy Jan 16 '18 at 15:42
  • @leonbloy If you transfer database let's say from India to France, how can you see India's local timestamp? E.g. item was added to database at 21:25 local (India) time and you want to see same time (21:25) in France as well. Instead you'll see something like 16:55 + India's timezone. – Michal Špondr Jan 16 '18 at 15:56
  • You need to take advantage of TIMESTAMP vs TIMESTAMPTZ data types. See eg my answer here https://stackoverflow.com/questions/6627289/what-is-the-most-recommended-way-to-store-time-in-postgresql-using-java/6627999#6627999 – leonbloy Jan 16 '18 at 16:03

1 Answers1

2

example:

t=# create database z;
CREATE DATABASE
t=# alter database z set timezone to 'UTC+2';
ALTER DATABASE
t=# select now();
             now
------------------------------
 2018-01-17 08:41:10.12834+00
(1 row)
t=# \c z
You are now connected to database "z" as user "postgres".
z=# select now();
              now
-------------------------------
 2018-01-17 06:41:15.588483-02
(1 row)

as you can see after connecting to database z client "default" TimeZone changed. But you should not think, that dates changed in the database - no. all timezone aware timestamps are kept in UTC.

For timestamp with time zone, the internally stored value is always in UTC

Now back to your question. Indeed:

z-# \! pg_dump -d z  | grep "TimeZone"

if you backup database z, no its settings exported. So you need to use pg_dumpall:

z-# \! pg_dumpall | grep "TimeZone"
ALTER DATABASE z SET "TimeZone" TO 'UTC+2';
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132