Azure Database for PostgreSQL time not showing correctly

Question

Azure Database for PostgreSQL time not showing correctly

Johan van der Walt 11

I was wondering if somebody here would be able to assist,
when running the query "SELECT NOW();" on Azure Database for PostgreSQL,
the value returned is not correct, I can see the time zone reflects correctly,
however the actual timestamp returned differs from the expected result.

is there any way I can change this?
I've tried to look for documentation online, but I've only found documentation relating to Azure SQL.

1 answer

Your answer

Answer 1

Saurabh Sharma 23,846 Microsoft Employee Moderator

Hi @Johan van der Walt ,

Thanks for using Microsoft Q&A !! I am getting the correct results on PostGreSQL server with SELECT Now();. What's the time/timezone you are expecting. Can you please provide a screenshot ?

Also, you can check your current PostgreSQL server timezone setting using the below query:

SELECT current_setting('TIMEZONE');

If your timezone is not correct then you can use SET TIME ZONE to change it for specific region using the timezone abbreviation. Reference - SET TimeZone

You can also use the below query to find list of available timezones - SELECT * FROM pg_timezone_names;

Other PostGreSQL documents you can refer to -

Thanks Saurabh

Johan van der Walt 11 Reputation points

2021-07-21T10:58:23.85+00:00

Thank you saurabhsharma-msft,

just on your previous response,
I've set the Timezone within Azure to "Asia/Almaty" (UTC +6)

but when running "SELECT NOW();" on the DB, it reports as UTC +2
(server is hosted within South-Africa North)

I just need to make these changes persistent,
any way of doing so?
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-21T15:19:55.843+00:00

Hi @Johan van der Walt ,

Thanks for sharing details. I have tried setting timezone to 'Asia/Almaty' using server parameters blade for a single server and it works fine for my server and current setting display 'Asia/Almaty'.

Ideally server parameters should be applied to the database permanently.
I am checking internally on this issue and get back to you. Also, is this a single server or a Flexible server ?

Thanks
Saurabh
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-21T17:52:08.697+00:00

Can you please try to apply the parameter again and share the result of the below query -

select * from pg_settings where name = 'TimeZone';

Thanks
Saurabh

Johan van der Walt 11

Thank you SaurabhSharma-msft,

currently Azure database for PostgreSQL instance is a single database instance,
I've also tried to restart the SQL instance after the applied config changes using server parameters blade.
still time is reported in UTC +2

running the following snippet:

select * from pg_settings where name = 'TimeZone';

yields:

I've taken a screenshot from DBeaver in this instance,
as to align the columns for visibility,

text output of the same query:

name    |setting      |unit|category                                          |short_desc                                                     |extra_desc|context|vartype|source|min_val|max_val|enumvals|boot_val|reset_val    |sourcefile|sourceline|pending_restart|  
--------|-------------|----|--------------------------------------------------|---------------------------------------------------------------|----------|-------|-------|------|-------|-------|--------|--------|-------------|----------|----------|---------------|  
TimeZone|Africa/Harare|    |Client Connection Defaults / Locale and Formatting|Sets the time zone for displaying and interpreting time stamps.|          |user   |string |client|       |       |NULL    |GMT     |Africa/Harare|          |          |false          |

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-22T18:32:33.883+00:00

@Johan van der Walt Thanks for sharing the information. PG needs to check in telemetry all the requests made in this case and thus you need to create a support ticket for this issue.

Thanks
Saurabh
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-27T18:59:30+00:00

@Johan van der Walt We haven't heard back from you. Just wanted to check if you are you still facing the issue ? or need any help for creating a support ticket.

Thanks
Saurabh
Johan van der Walt 11 Reputation points

2021-07-28T07:12:05.59+00:00

Saurabh apologies for the delay in response,

issue is still not resolved as of yet,
as you've suggested, I've created a support case,
however, I've not received any response from the team yet.

if possible, can you maybe just have a look on your side?
I've cc'd you in the original e-mail sent on Friday the 23rd of July 2021

regards,
Johan
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-28T23:27:34.197+00:00

Hi @Johan van der Walt ,
Sorry, I do not see this mail in my inbox. Please share the case number over the email which I have sent now.

Thanks
Saurabh

Share via

Azure Database for PostgreSQL time not showing correctly

1 answer

Your answer