1

I'd like to know how to permanently change the timezone on postgresql -- using set timezone TO 'UTC' only changes it in current session, upon restarting it's back to my region timezone. If i change the postgresql.config file under data to:

 datestyle = 'iso, dmy'

 #intervalstyle = 'postgres'

 timezone = 'UTC'

 #timezone_abbreviations = 'Default'

Even if i stop the postgresql-x64-12 service, save the file, and restart service, it goes back to my region timezone afterwards.

Thank you!

richyen
  • 8,114
  • 4
  • 13
  • 28
LaioZatt
  • 23
  • 1
  • 5
  • Possible duplicate of [postgres default timezone](https://stackoverflow.com/questions/6663765/postgres-default-timezone) – Simon Notley Oct 21 '19 at 20:34
  • Does this answer your question? [How to set timezone for Postgres 9.4 permanently](https://stackoverflow.com/questions/32423037/how-to-set-timezone-for-postgres-9-4-permanently) – Henry Woody Feb 13 '22 at 00:39

1 Answers1

2

The documentation says:

The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 19. There are also some special ways to set it:

  • The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

  • The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

So either you changed the value in a wrong postgresql.conf, or you have the PGTZ environment variable set on the client side.

Another alternative is a SET TIME ZONE command in the .psqlrc file.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I thought that maybe i was changing the wrong value or wrong file, but im pretty sure thats not the case. Im checking directly on server via DBeaver so no client there. Its probably something else in the DBeaver or in the postgre by itself changing the value on startup or something. That 'set time zone' command in .psqlrc file changes permanently? Will look into that, thank you! – LaioZatt Oct 23 '19 at 18:57
  • No, `SET` changes a parameter for the session only. What do you get for `SELECT source, sourcefile, sourceline FROM pg_settings WHERE name = 'timezone';`? – Laurenz Albe Oct 24 '19 at 05:47