PostgreSql: Getting strange-formated "timestamp with time zone"

Question

I am inserting into the table with field type "timestamp with time zone" string "1858-11-17 01:09:05+0000" and getting back strage formated value "05:11:29+04:02:24".

Here is 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 timezone

% date +%Z
YEKT
% date +%z
+0600

Don't know but it happens for any date before 1914-01-01 UTC — Clodoaldo Neto, May 15 '13 at 18:49
I'm not seeing this on 9.1.9. What is your locale set to, maybe relevant? — Chris Farmiloe, May 15 '13 at 18:58
I'm on Linux 9.2.4 BRT time zone. It does not happen in [SQL Fiddle 9.2.4](http://www.sqlfiddle.com/#!12/d41d8/940) probably because the server is set at UTC — Clodoaldo Neto, May 15 '13 at 19:02
Confirm. Setting timezone='utc' solves the problem. But what was it ? A bug ? — s9gf4ult, May 15 '13 at 19:28

score 2 · Accepted Answer · answered May 15 '13 at 19:38

2

This is an effect of the time zone. Before early 20th century many countries (like Germany or Russia) had completely different regimes like "mean solar time" which would not translate cleanly to UTC.

Therefore a time in time zone 0 (GMT at the time, as there was no UTC yet) would have an odd time offset when represented as local time for Yekaterinburg (Russia).

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

answered May 15 '13 at 19:38

Erwin Brandstetter

605,456
145
1,078
1,228

so, this is 4 hours, 2 minutes and 24 secs ? – s9gf4ult May 15 '13 at 19:57

Chris Farmiloe · Answer 2 · 2013-05-15T19:37:15.683

-1

It is interpreting 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 the just 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)

edited May 15 '13 at 19:37

answered May 15 '13 at 19:12

Chris Farmiloe

13,935
5
48
57

Your answer is not related to question. Why is this happening and what is "+04:02:24" ? Your results does not describe this. – s9gf4ult May 15 '13 at 19:22
The +04:02:24 is the time offset from UTC into your locale – Chris Farmiloe May 15 '13 at 19:35
also see the [datestyle](http://www.postgresql.org/docs/8.2/static/runtime-config-client.html#GUC-DATESTYLE) parameter – Chris Farmiloe May 15 '13 at 19:39

PostgreSql: Getting strange-formated "timestamp with time zone"

2 Answers2

Linked