0

I set the Postgres timezone to UTC using:

SET timezone = 'UTC';

But then when I do:

select now();

It returns the following result:

2023-06-04 14:06:08.428 +0200

I was expecting the result to just be in UTC format: 2023-06-04 12:06:08.428.

I am aware that I can use select now()::timestamp to get the UTC value, but I am wondering:

Where does this +0200 come from, if I set the timezone to UTC using SET?

Thank you.

wonderingdev
  • 1,132
  • 15
  • 28
  • I can't reproduce this. Are you sure you are doing the same thing that you are asking about? – wilx Jun 04 '23 at 12:22
  • @wilx Yes, am sure. I execute these 2 commands: SET timezone = 'UTC'; select now(); – wonderingdev Jun 04 '23 at 12:27
  • 1) I cannot reproduce. 2) What is the default timezone set in `postgresql.conf`? 3) What client are you using to run the commands? 4) `select now()::timestamp` will not get the `UTC` value it will just be `2023-06-04 14:06:08.428`. – Adrian Klaver Jun 04 '23 at 15:36
  • @AdrianKlaver 2) The default timezone in .conf file is: timezone = 'Etc/UTC' 3) I am using DBeaver. Also I am using the postgis/postgis:15-3.3 docker image. 4) Oh okay, thank you for the information. – wonderingdev Jun 04 '23 at 16:10
  • @IvanGandacov. Using `psql` do: 1) `SHOW timezone;` and if not `UTC`, then `SET timezone = 'UTC';`. 2) `select now();`. – Adrian Klaver Jun 04 '23 at 16:44
  • I cannot reproduce that either. Perhaps you are running the two statements in different database sessions (connection pool). – Laurenz Albe Jun 04 '23 at 20:14
  • 1
    @LaurenzAlbe, I think it is related to [DBeaver Timezone](https://stackoverflow.com/questions/45323552/how-to-change-dbeaver-timezone-how-to-stop-dbeaver-from-converting-date-and-ti/72533665#72533665). – Adrian Klaver Jun 04 '23 at 21:02
  • @AdrianKlaver Yes, just set the DBeaver Timezone to UTC, and the output is in UTC. Thank you! Does it mean that probably the DBeaver JDBC driver converts the output to its own timezone? – wonderingdev Jun 05 '23 at 19:25
  • Pretty sure yes. The way to confirm is to crank up the Postgres logging(temporarily) in `postgresql.conf` [What to log](https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT) *log_statement = 'all'*. Then look at the Postgres log to see what happens when you connect. – Adrian Klaver Jun 05 '23 at 19:37

1 Answers1

0

It seems that the best way to get UTC time is to ask for now() AT TIME ZONE 'UTC'.

wilx
  • 17,697
  • 6
  • 59
  • 114