Re: BUG #15145: date time default value issues

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: "dibxyooj96(at)gmail(dot)com" <dibxyooj96(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #15145: date time default value issues
Date: 2018-04-07 14:14:18
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Friday, April 6, 2018, PG Bug reporting form <noreply(at)postgresql(dot)org>
> wrote:
>> i set column "document_datenow timestamp with time zone NOT NULL DEFAULT
>> now()"
>> but the values that like this "0001-12-31 23:42:04+06:42:04 BC"

> This is so unusual I don't know really where to start.

Poking about in the tz database, I notice that +06:42:04 is the UTC offset
shown for Asia/Bangkok before 1920 (ie, before adoption of a standard time
zone offset). So, if the database's time zone were set to Asia/Bangkok
and then you put in a time around the start of the common era:

regression=# set timezone = 'Asia/Bangkok';
regression=# select '0001-12-31 17:00 UTC BC'::timestamptz;
0001-12-31 23:42:04+06:42:04 BC
(1 row)

Now this just moves the mystery to another place: how'd that value get
entered? I'd speculate about perhaps using to_timestamp() with a wrong
format string, causing what should have been year 2001 to be read as 0001,
or something like that. The OP hasn't shown us what he did to enter this
value, so it's all speculation. The table definition is nigh irrelevant,
though :-(

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-04-07 15:51:32 Re: BUG #14999: pg_rewind corrupts control file global/pg_control
Previous Message Marko Tiikkaja 2018-04-07 10:08:25 Re: BUG #15145: date time default value issues