Thread: BUG #2366: Timestamptz data type is inaccurate

The following bug has been logged online:

Bug reference:      2366
Logged by:          Christian Holtje
Email address:      christian.holtje@timesys.com
PostgreSQL version: 7.4.8
Operating system:   Ubuntu 5.10
Description:        Timestamptz data type is inaccurate
Details:

I don't know what's going on, but basically, for some dates timestamptz is
loosing parts of the date/time (like the timezone, etc.)

db=# select '2076-03-30 19:57:05-05'::timestamptz;
     timestamptz
---------------------
 2076-03-31 00:57:05
(1 row)

db=# select '2037-03-30 19:57:05-05'::timestamptz;
      timestamptz
------------------------
 2037-03-30 20:57:05-04
(1 row)

db=# select '2006-03-30 19:57:05-05'::timestamptz + '100 years'::interval;
      ?column?
---------------------
 2106-03-30 19:57:05
(1 row)

Note that the hours, minutes or timezone aren't correct.  I tried the last
one to make sure it's not due to the parser.

Thanks for your help.

Christian Holtje wrote:

> I don't know what's going on, but basically, for some dates timestamptz is
> loosing parts of the date/time (like the timezone, etc.)
>
> db=# select '2076-03-30 19:57:05-05'::timestamptz;
>      timestamptz
> ---------------------
>  2076-03-31 00:57:05
> (1 row)

Timestamp support went under a big rewrite for 8.0.  The problem doesn't
present there.  I suggest you upgrade; the code in 7.4 is probably buggy
enough that it would be a difficult proposition to fix the bug there.

That said, maybe you could try with the --enable-integer-datetimes flag
to configure -- if you are lucky the bugs are not in that code path.
But I wouldn't count on it.

(I can reproduce the bug here; my 7.4 build has floating point
datetimes.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Christian Holtje" <christian.holtje@timesys.com> writes:
> I don't know what's going on, but basically, for some dates timestamptz is
> loosing parts of the date/time (like the timezone, etc.)

> db=# select '2076-03-30 19:57:05-05'::timestamptz;
>      timestamptz
> ---------------------
>  2076-03-31 00:57:05
> (1 row)

That date is probably outside the range of dates for which your OS knows
the timezone rules.  If you were using PG 8.0 or later, this would
actually be our fault, because 8.0 contains its own timezone database
... but 7.4 is at the mercy of the OS's timezone library.

            regards, tom lane