All geek questions in one place

PostgreSql: getting the weird "time zone timestamp"

I insert into the table a line like "timestamp with time zone", the line "1858-11-17 01: 09: 05 + 0000" and returning the formatted value "05: 11: 29 + 04: 02: 24".

Here is a session

test=> create table ddtbl (val timestamp with time zone); CREATE TABLE test=> insert into ddtbl (val) values ('1858-11-17 01:09:05+0000'); INSERT 0 1 test=> select * from ddtbl; val ------------------------------ 1858-11-17 05:11:29+04:02:24

Why is this happening and what is "+04: 02: 24" here?

UPD: PostgreSQL version

 % psql --version psql (PostgreSQL) 9.2.4

UPD2: Local Time Zone

 % date +%Z YEKT % date +%z +0600
+4
source share
2 answers

This is a time zone effect. Until the beginning of the 20th century, many countries (for example, Germany or Russia) had completely different regimes, such as "average solar time", which would not translate purely into UTC.

Therefore, time in time zone 0 (GMT at the time when there was no UTC) will have an odd time offset if it is presented as local time for Yekaterinburg (Russia).

+04:02:24 - the actual offset compared to UTC.

+2
source

The interpretation of your input value is UTC.

 psql=# select cast('1858-11-17 01:09:05 UTC' as timestamp with time zone); timestamptz ------------------------ 1858-11-17 01:09:05+00 (1 row) psql=# select cast('1858-11-17 01:09:05 BRT' as timestamp with time zone); timestamptz ------------------------ 1858-11-17 04:09:05+00 (1 row)

The two values ​​are simply different representations of the same timestamp.

 psql=# select cast('1858-11-17 05:11:29+04:02:24' as timestamp with time zone) = cast('1858-11-17 01:09:05+0000' as timestamp with time zone); ?column? ---------- t (1 row)
-1
source

Source: https://habr.com/ru/post/1481011/

More articles:


All Articles