1

I've got a hard time with postgresql and timezones.

The Postgresql server is in UTC time zone. I want to perform a query that returns the date in another timezone with the timezone offset.

I can perform a query that returns the date in a timezone but I'm missing the timezone offset

base=# SET timezone to 'utc';
SET
base=# select now();
             now              
------------------------------
 2018-04-20 14:58:22.68038+00
(1 row)

base=# SET timezone to 'Europe/Paris';
SET
base=# select now();
              now              
-------------------------------
 2018-04-20 16:58:29.614383+02
(1 row)


base=# SET timezone to 'utc';
SET
base=# select now() AT TIME ZONE 'Europe/Paris';
          timezone          
----------------------------
 2018-04-20 16:59:03.146917   -- missing timezone offset here
(1 row)

Expected result

base=# SET timezone to 'utc';
SET
base=# select now() AT TIME ZONE 'Europe/Paris'; --I'm missing something here, I guess
          timezone          
----------------------------
 2018-04-20 16:59:03.146917+02   -- That's what I want
(1 row)

Do you have any idea how to do it?

Thanks

Zoyd
  • 3,449
  • 1
  • 18
  • 27
Jeff
  • 979
  • 3
  • 10
  • 18

2 Answers2

2

If you use AT TIME ZONE, PG returns a timestamp without time zone. If you cast it to back to a timestamp with time zone, you get your expected result:

SELECT (NOW() AT TIME ZONE 'Europe/Paris')::TIMESTAMPTZ;

Result:

2018-04-20 18:15:26.165+02

How do you get the +02 without setting the timezone? Here's the problem: NOW() AT TIME ZONE 'Europe/Paris' returns the current time in the specified time zone (without an offset), and casting it back to TIMESTAMPTZ gives you the offset of the current time zone (of your PG session) based on UTC. Since 'Europe/Paris' is +02 from UTC, if you've set your time zone to 'Europe/Paris', the offset you get in a TIMESTAMPTZ is +02. If your current time zone setting is UTC, your offset is +00.

How to get the offset no matter what your PG session's time zone setting, without explicitly setting the time zone? I would like to think there's a better way to get the offset, but without knowing one, here's one way: calculate the offset, format it, append it to the timestamp without time zone.

SELECT (NOW() AT TIME ZONE 'Europe/Paris')::TEXT || TO_CHAR(EXTRACT(hour FROM NOW() AT TIME ZONE 'Europe/Paris' - NOW() AT TIME ZONE 'UTC'), 'FMSG00')

Result: 2018-04-21 15:12:42.658+02, and I get the same result no matter the current timezone.

404
  • 8,022
  • 2
  • 27
  • 47
  • Thanks, it gives the expected result only if we use `set time zone 'Europe/Paris';` Then we just have to use: ` SELECT (NOW())::TIMESTAMPTZ;` to get: `2018-04-21 13:31:12.088679+02`. I would like to just get this result without explicitly setting the timezone with `SET TIME ZONE 'Europe/Paris';` – Jeff Apr 21 '18 at 11:31
0

  • Some timezone have an offset in 30 or 45 minutes. Using TO_CHAR(EXTRACT(hour FROM NOW() AT TIME ZONE 'Europe/Paris' - NOW() AT TIME ZONE 'UTC') exclude this use case.

  • Did you consider to use SET LOCAL TIME ZONE 'Europe/Paris'; in a dedicated function ? According to the documentation :

If SET LOCAL is used within a function that has a SET option for the same variable (see CREATE FUNCTION), the effects of the SET LOCAL command disappear at function exit

This other reply seem's fitting your requirement : How to convert timestamp field to ISO 8601 string in a given time zone?

Martin Mouterde
  • 1