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 ([EMAIL PROTECTED])
Elein Mustain
NextBus Information Systems
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
