Timestamp, fractional seconds problem

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