Timestamp, fractional seconds problem

From: Laurette Cisneros <laurette(at)nextbus(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Timestamp, fractional seconds problem
Date: 2001-10-04 00:02:59
Message-ID: Pine.LNX.4.33.0110031659120.26623-100000@visor.corp.nextbus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc


Problem: the external representation of time and timestamp are
less precise than the internal representation.

We are using postgresql 7.1.3

The timestamp and time types support resolving microseconds (6 places beyond the decimal), however the output routines round the value to only 2 decimal places.

This causes data degradation, if a table with timestamps is copied out and then copied back in, as the timestamps lose precision.

We feel this is a data integrity issue. Copy out (ascii) does not maintain the consistency of the data it copies.

In our application, we depend on millisecond resolution timestamps and often need to copy out/copy back tables. The current timestamp formating in postgresql 7.1.x breaks this badly.

A work around for display might be to use to_char(). But for copy the only workaround we have found is to use binary copy. Alas, binary copy does not work for server to client copies.

Unfortunately, we need to copy to the client machine. The client copy does not support binary copies so we lose precision.

Our suggested fix to this problem is to change the encoding of the fractional seconds part of the datetime and time types in datetime.c
(called by timestamp_out, time_out) to represent least 6 digits beyond the decimal (ie "%0.6f"). A configurable format would also work.

If there is another way to force the encoding to be precise we'd love to hear about it. Otherwise this appears to be a silent data integrity bug with unacceptable workarounds.

Thanks!

Laurette Cisneros (laurette(at)nextbus(dot)com)
Elein Mustain

NextBus Information Systems

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2001-10-04 02:16:42 Re: Unicode combining characters
Previous Message John Summerfield 2001-10-03 23:53:43 Re: cvs problem

Browse pgsql-jdbc by date

  From Date Subject
Next Message Thomas Lockhart 2001-10-04 04:39:19 Re: Timestamp, fractional seconds problem
Previous Message David Siebert 2001-10-03 21:26:29 Still getting the timestamp error.