question

JohanvanderWalt-2586 avatar image
0 Votes"
JohanvanderWalt-2586 asked SaurabhSharma-msft answered

Azure Database for PostgreSQL time not showing correctly

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.

azure-database-postgresql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered

Hi @johanvanderwalt-2586,

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');
116521-image.png

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;
116523-image.png

Other PostGreSQL documents you can refer to -
- Date/Time Functions and Operators
- System Administration Functions

Thanks
Saurabh



image.png (4.8 KiB)
image.png (33.2 KiB)
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you saurabhsharma-msft,

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

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

I just need to make these changes persistent,
any way of doing so?


0 Votes 0 ·
psql-config.png (61.8 KiB)
query-result.png (6.9 KiB)

Hi @johanvanderwalt-2586,

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'.
116709-image.png
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


0 Votes 0 ·
image.png (8.6 KiB)

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

0 Votes 0 ·

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:

117037-test.png

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


0 Votes 0 ·
test.png (20.6 KiB)
Show more comments