BUG #16541: Timestamp allowing greater than max documented value?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16541: Timestamp allowing greater than max documented value?

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      16541
Logged by:          Michael McLaughlin
Email address:      [hidden email]
PostgreSQL version: 9.6.16
Operating system:   CentOS7
Description:        

I discovered while copying data from a PostgreSQL 9.6.16 database to a
PostgreSQL 12.2 database that some of my imports were failing because
timestamps in my data are out of range. As it turns out, somehow we wrote
erroneous future dates into timestamp columns in our PG 9.6.16 database
(i.e. '1666771-01-01 00:00:00') and the 9.6.16 allowed this, but when
attempting to copy the data to the 12.2 database I get the out of range
error. Per the PG documentation, the max value for timestamp is the year
294276 AD and this has been the case since version 8.4, but obviously it is
still being allowed in version 9.6.16.

For a very simple demonstration, in my 9.6.16 database running the command
"select '1666771-01-01 00:00:00'::timestamp;" returns the horribly
futuristic timestamp. Executing the same command in my 12.2 database returns
ERROR:  timestamp out of range: "1666771-01-01 00:00:00" as would be
expected for both.

Ultimately I know we need to get the data corrected or removed prior to
upgrading, I am simply trying to find the history of this change and more
importantly on what version it was actually changed/resolved.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16541: Timestamp allowing greater than max documented value?

Tom Lane-2
PG Bug reporting form <[hidden email]> writes:
> I discovered while copying data from a PostgreSQL 9.6.16 database to a
> PostgreSQL 12.2 database that some of my imports were failing because
> timestamps in my data are out of range. As it turns out, somehow we wrote
> erroneous future dates into timestamp columns in our PG 9.6.16 database
> (i.e. '1666771-01-01 00:00:00') and the 9.6.16 allowed this, but when
> attempting to copy the data to the 12.2 database I get the out of range
> error. Per the PG documentation, the max value for timestamp is the year
> 294276 AD and this has been the case since version 8.4, but obviously it is
> still being allowed in version 9.6.16.

I believe the actual story is that floating-point timestamps allow a much
wider date range than integer timestamps (with corresponding loss of
precision as you get further away from the epoch date).  The default
for integer_datetimes changed to "on" in 8.4, but your 9.6 installation
must have been built with it turned off.

This is documented, if not too prominently.  If you check

https://www.postgresql.org/docs/9.6/datatype-datetime.html

the second "Note" includes

        Note that using floating-point datetimes allows a larger range of
        timestamp values to be represented than shown above: from 4713 BC
        up to 5874897 AD.

That note is gone in more recent branches because we removed the
floating-point timestamp support altogether.

                        regards, tom lane