Thread: 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 >