Обсуждение: BUG #4960: Unexpected timestamp rounding

The following bug has been logged online:

Bug reference:      4960
Logged by:          Matthias
Email address:      matthias.cesna@gmail.com
PostgreSQL version: 8.3.7
Operating system:   Windows XP
Description:        Unexpected timestamp rounding
Details:

Dear developers,

I noticed an unusual (and from my point of view inconsistent) rounding of a
timestamp:

It is about when using a upper-boundary timestamp. The value of 9999-12-31
23:59:59.999999 is sometimes used to indicate an infinite validity. However
passing such a value in an SQL will lead it to be rounded up:

DDB02=# select timestamp '9999-12-31 23:59:59.999999';
      timestamp
----------------------
 10000-01-01 00:00:00
(1 row)

This is not logical to me since 23:59:59.999999 would be a valid value or?
This also happens during the insert into a table and as such modifies my
intention and the data.

Doing the same with the year 2000 does not give me any rounding:

DDB02=# select timestamp '2000-12-31 23:59:59.999999';
         timestamp
----------------------------
 2000-12-31 23:59:59.999999

Thank you for having a look at the issue & sincerely thank you for such a
great database!

Regards,
Matthias

"Matthias" <matthias.cesna@gmail.com> wrote:

> I noticed an unusual (and from my point of view inconsistent)
> rounding of a timestamp:

What do you get when you run?:

show integer_datetimes;

If it is off, which is probably the default for your distribution
under 8.3.X, timestamps are floating point (approximate) values which
get less precise as you move away from the base timestamp of
'2000-01-01 00:00'.

The default under 8.4 is to use integer timestamps, which have a
microsecond precision across the range they support.  (That range is
not as broad as the floating point format, but plenty large for most
practical uses.)

You can configure PostgreSQL to use integer timestamps in 8.3 if you
build from source, but you will need to convert your database.

-Kevin

"Matthias" <matthias.cesna@gmail.com> wrote:

> It is about when using a upper-boundary timestamp. The value of
> 9999-12-31 23:59:59.999999 is sometimes used to indicate an infinite
> validity.

One other thought -- using a "magic value" for something like this is
usually a bad idea.  NULL indicates the absence of a value, and means
"unknown or not applicable".  I generally use that for an upper bound
when there is no valid upper bound.  In particular, expiration or end
dates which will probably eventually be fixed, but haven't been yet,
are more appropriately NULL.  It isn't that there won't be one; it
just isn't known yet -- which fits the semantics of NULL very well.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "Matthias" <matthias.cesna@gmail.com> wrote:
>> It is about when using a upper-boundary timestamp. The value of
>> 9999-12-31 23:59:59.999999 is sometimes used to indicate an infinite
>> validity.

> One other thought -- using a "magic value" for something like this is
> usually a bad idea.  NULL indicates the absence of a value, and means
> "unknown or not applicable".  I generally use that for an upper bound
> when there is no valid upper bound.

Also, if you really want to convey the idea of "infinity" rather than
"unknown", the timestamp types do have special values 'infinity'
and '-infinity', which are likewise far preferable to choosing magic
regular values.

            regards, tom lane

Dear Tom, dear Kevin

In fact "show integer_datetimes;" returns "off". As such there is a perfect
reason for this rounding. I assumed wrongly this being a bug. Sorry :-)

I understand as well your arguments on why not to use such a value for
infinity. The reason why I used it was because I ported this data from a
mainframe DB2 database where this value by tradition represented a high
value and NULL values not being used for compatibility in regards to the
mapped data type in PL1 and pure text-file processing of the same data. In
these programs the length of the year part of a timestamp is limited to 4
digits. This is where I noted the rounding which had occurred as a result of
my data imports into Postgresql.

Thanks again for your information and sorry for the disturbance :-)
Regards,
Matthias

On Fri, Jul 31, 2009 at 11:00 PM, Kevin Grittner <
Kevin.Grittner@wicourts.gov> wrote:

> "Matthias" <matthias.cesna@gmail.com> wrote:
>
> > I noticed an unusual (and from my point of view inconsistent)
> > rounding of a timestamp:
>
> What do you get when you run?:
>
> show integer_datetimes;
>
> If it is off, which is probably the default for your distribution
> under 8.3.X, timestamps are floating point (approximate) values which
> get less precise as you move away from the base timestamp of
> '2000-01-01 00:00'.
>
> The default under 8.4 is to use integer timestamps, which have a
> microsecond precision across the range they support.  (That range is
> not as broad as the floating point format, but plenty large for most
> practical uses.)
>
> You can configure PostgreSQL to use integer timestamps in 8.3 if you
> build from source, but you will need to convert your database.
>
> -Kevin
>