PostgreSQL time zones

Question

I'm trying to understand how the session time zone works in PostgreSQL.

When I set session time zone to '01:00', returned date is in '-01:00'. Sign is always inverted and I do not understand this behavior.

How can this be explained?

postgres=# set time zone '00:00';
SET
postgres=# select timestamp with time zone '2017-11-29 15:00:00.000000+00';
      timestamptz       
------------------------
 2017-11-29 15:00:00+00
(1 row)

postgres=# set time zone '01:00';
SET
postgres=# select timestamp with time zone '2017-11-29 15:00:00.000000+00';
      timestamptz       
------------------------
 2017-11-29 14:00:00-01
(1 row)

postgres=# set time zone '-01:00';
SET
postgres=# select timestamp with time zone '2017-11-29 15:00:00.000000+00';
      timestamptz       
------------------------
 2017-11-29 16:00:00+01
(1 row)

Wonder if it’s a side effect of this mentioned in the documentation about positive and negative being reversed in POSIX (mobile copy paste isn’t working): postgresql.org/docs/9.6/static/… — Sami Kuhmonen
– Sami Kuhmonen, Commented Nov 29, 2017 at 14:35
But that would be very weird, as minus will mean a thing in the SET command and another thing in the resulted value. — Adriano dos Santos Fernandes
– Adriano dos Santos Fernandes, Commented Nov 29, 2017 at 14:54
Yes, as the documentation says, there are cases where they are reversed — Sami Kuhmonen
– Sami Kuhmonen, Commented Nov 29, 2017 at 14:55
So, for Brazil (GMT-2 in daylight saving time), I should set my timezone to '+02:00' ? — Adriano dos Santos Fernandes
– Adriano dos Santos Fernandes, Commented Nov 29, 2017 at 15:07
I would always keep database and the data in it in UTC and handle any timezone conversions in the code using it. It makes things a lot easier. But I don’t know if you need different things. Also can’t say for sure if this is the issue and if that is the way to fix it, just one possibility based on the docs I’ve seen. — Sami Kuhmonen
– Sami Kuhmonen, Commented Nov 29, 2017 at 15:09

Vao Tsun · Accepted Answer · 2017-11-30 09:03:52Z

example, here

ts is time (text) - just time as we say it, :
not_aware the time if we don't know its timezone (local to the saying person)
aware the time, but knowing the timezone of it(if server time zone is UTC, then of utc zone)

, so:

s=# with s(city,tz) as (values('Moscow','UTC+3'),('New York','UTC-5'))
, ts as (select '2017-01-01 00:00:00'::text ts)
select *,ts::timestamp at time zone tz not_aware, ts::timestamptz at time zone tz aware
from s
join ts on true;
   city   |  tz   |         ts          |       not_aware        |        aware
----------+-------+---------------------+------------------------+---------------------
 Moscow   | UTC+3 | 2017-01-01 00:00:00 | 2017-01-01 03:00:00+00 | 2016-12-31 21:00:00
 New York | UTC-5 | 2017-01-01 00:00:00 | 2016-12-31 19:00:00+00 | 2017-01-01 05:00:00
(2 rows)

lets say you you come from Lodon and have have watches that show UTC0 time - local for London (this is your server with timezone UTC0). Now are fly to Moscow, looking at the Kremlin Clock and you see the ts, midnight. Your watches are aware of time difference and they show you that to Ney Year is in three hours (this is timestamptz - aware column). This is why UTC+3 requires to minus the time. Because for your server Moscow time is in future, thus when you see local 2017, for server it is still 2016 and gonna be like it for +3 hours...

Now the behaviour that you expected (not_aware column) is more tricky. The server runs at UTC0, but has to pretend it know nothing about the "real time". As timestamp without time zone is such type. So it behaves as if you were watching new year celebration on Red Square on TV and would just scroll the bar to adjust your watches to Kremlin Clock, so in fact the time you see is your London time +3 (not_aware) column.

My doubts is in relation to the session time zone and why it's reversed. It doesn't make sense for me to set session time zone to '+02:00' to have the behaviour of -02:00.

Ethan · Accepted Answer · 2019-05-01 00:26:53Z

0

I came across this while looking for answers to other questions.

In case you never figured it out:

I think this is a quirk of the timezone parsing code trying to follow POSIX syntax, as noted above. You get the same behavior by e.g. SET TIME ZONE 'CET1:00'; or SET TIME ZONE 'CET1'.
I think the "actual" SQL way of saying what you were trying to say is SET TIME ZONE 1.
You probably don't want to have this be set as your timezone anyhow because it doesn't track DST changes. You probably want to choose a timezone by name from the tz database, for example America/New_York or (going with the GMT+1 example) Europe/Madrid.

answered May 1, 2019 at 0:26

Ethan

1312 silver badges6 bronze badges

Collectives™ on Stack Overflow

PostgreSQL time zones

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related