How do I change the timezone of Postgres database in the Azure Cloud

Question

How do I change the timezone of Postgres database in the Azure Cloud

John Butler 1

I need to change the timezone of one of my Postgres SQL databases I have in the MS Azure cloud from America/Los_Angeles to UTC.

I have gone to the Server Parameters page and changed the timezone to UTC, but when I connect to the database using psql or other tool and do a select current_timestamp when connected to the database is still returns the time in America/Los_Angeles time.

Also when I do a show timezone it returns America/Los_Angeles.

2 answers

Your answer

Answer 1

Takahito Iwasa 4,851 MVP Volunteer Moderator

Hi. @John Butler

I changed the timezone in Single Server of Azure Database for Postgresql and checked it. (Asia / Tokyo <-> UTC)

It worked fine.

   postgres=> show timezone;  
     TimeZone    
     
   \------------  
     
    Asia/Tokyo  
   (1 row)  
     
   postgres=> select * from current_timestamp;  
          current_timestamp         
     
   \-------------------------------  
     
    2022-03-31 16:15:31.805027+09  
   (1 row)  



   postgres=> show timezone;  
    TimeZone   
     
   \----------  
     
    UTC  
   (1 row)  
     
   postgres=> select * from current_timestamp;  
          current_timestamp         
     
   \-------------------------------  
     
    2022-03-31 07:16:13.477177+00  
   (1 row)

Did you save after making changes on the parameter screen?
Or does the server you are connecting to match the server you are checking on the portal?

Takahito Iwasa 4,851 Reputation points MVP Volunteer Moderator

2022-03-31T07:38:44+00:00
If you haven't forgotten to save it, there is another possibility.
If the database has a time zone set, you can specify the time zone directly with ALTER DATABASE.

postgres=> show timezone; TimeZone ---------- UTC (1 row) postgres=> ALTER DATABASE postgres SET timezone TO 'Asia/Tokyo'; ALTER DATABASE --- quit and reconnect --- postgres=> show timezone; TimeZone ------------ Asia/Tokyo (1 row)

https://dev.classmethod.jp/articles/azure-database-for-postgresql-server-parameter-timezone/

In this case the server parameters are ignored.

If you want to prioritize server parameters, try the following:

ALTER DATABASE postgres RESET timezone
John Butler 1 Reputation point

2022-03-31T19:55:53.913+00:00

I did click the Save button on the Server Parameters page of the Azure console for the database I need to change. The timezone parameter on the page shows UTC.

I also connected to the database as the Admin user and issued the alter database postgres set timezone to 'UTC' command, but when I disconnected and reconnected the show timezone command still returned 'America/Los_Angeles' and a select current_timestamp still returned the current time in the America/Los_Angeles timezone, not in UTC.
Takahito Iwasa 4,851 Reputation points MVP Volunteer Moderator

2022-03-31T19:57:19.877+00:00

Thank you.
Now, which database are you connected to? Is it "postgres"?

Answer 2

John Butler 1

I figured this one out. The desktop tool I was using - DBVisualizer Pro 12 was intercepting my show timezone, select current_setting(), select now() and select current_timestamp commands and translating them to values contained in the Properties configuration for the tool.

When I issue these commands from native psql or pgAdmin4 the timezone parameter and current time both show UTC.

Thanks for your help

Share via

How do I change the timezone of Postgres database in the Azure Cloud

2 answers

Your answer