Re: BUG #15145: date time default value issues

The following bug has been logged on the website:

Bug reference: 15145
Logged by: Paj Dib Xyooj
Email address: dibxyooj96(at)gmail(dot)com
PostgreSQL version: 9.4.5
Operating system: (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
Description:

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"

On Friday, April 6, 2018, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15145
> Logged by: Paj Dib Xyooj
> Email address: dibxyooj96(at)gmail(dot)com
> PostgreSQL version: 9.4.5
> Operating system: (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
> Description:
>
> 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"
>

What does typing "date" at your o/s command line show?

This is so unusual I don't know really where to start. But 9.4.5 is an
unsupported minor release and your O/S version seems older as well. If
this isn't a production machine you need to diagnose I'd suggest installing
recent O/S and PostgreSQL, possibly from packages, and save yourself some
grief. Otherwise, after upgrading to 9.4.17 (though I would backup the
existing binaries just in case) you probably should volunteer considerably
more detail about your environment and what you were doing to it before you
discovered this behavior. Providing a self-contained script and output
transcript would be good too.

David J.

On Friday, April 6, 2018, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Friday, April 6, 2018, PG Bug reporting form <noreply(at)postgresql(dot)org>
> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 15145
>> Logged by: Paj Dib Xyooj
>> Email address: dibxyooj96(at)gmail(dot)com
>> PostgreSQL version: 9.4.5
>> Operating system: (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
>> Description:
>>
>> 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"
>>
>
> What does typing "date" at your o/s command line show?
>
>
So, that a constant time is used is not unusual, it's documented. Now() is
a function that gets resolved at default creation. You need to use
"current_timestamp" literal special value if you want insertion time
defaults. But since you didn't create the table in BC with an odd time
zone offset either you gave bad example or there is still something wrong
in your environment.

David J.

On Sat, Apr 7, 2018 at 12:35 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Friday, April 6, 2018, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Friday, April 6, 2018, PG Bug reporting form <noreply(at)postgresql(dot)org>
>> wrote:
>>
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 15145
>>> Logged by: Paj Dib Xyooj
>>> Email address: dibxyooj96(at)gmail(dot)com
>>> PostgreSQL version: 9.4.5
>>> Operating system: (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
>>> Description:
>>>
>>> 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"
>>>
>>
>> What does typing "date" at your o/s command line show?
>>
>>
> So, that a constant time is used is not unusual, it's documented. Now()
> is a function that gets resolved at default creation.
>

That's not correct. You're thinking of DEFAULT 'now'. DEFAULT now()
works just fine.

.m

"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';
SET
regression=# select '0001-12-31 17:00 UTC BC'::timestamptz;
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