
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@nextbus.com)
Elein Mustain

NextBus Information Systems



Wed, 3 Oct 2001 17:02:59 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Timestamp, fractional seconds problem
> Problem: the external representation of time and timestamp are
>           less precise than the internal representation.

Fixed (as of yesterday) in the upcoming release.

                       - Thomas

Thu, 04 Oct 2001 04:39:19 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Timestamp, fractional seconds problem
On Wed, Oct 03, 2001 at 05:02:59PM -0700, Laurette Cisneros wrote:

> A work around for display might be to use to_char().

 In 7.2 is possible use millisecond / microsecond format:

# select to_char(now()+'2s 324 ms'::interval, 'HH:MM:SS MS');
   to_char
--------------
 10:10:59 324
(1 row)

# select to_char(now()+'2s 324 ms 10 microsecon'::interval, 'HH:MM:SS US');
     to_char
-----------------
 10:10:03 324010
(1 row)

	Karel 

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Thu, 4 Oct 2001 11:01:26 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: Timestamp, fractional seconds problem
This is very good news.  Thanks to all for the response.

L.
On Thu, 4 Oct 2001, Karel Zak wrote:

> On Wed, Oct 03, 2001 at 05:02:59PM -0700, Laurette Cisneros wrote:
>
> > A work around for display might be to use to_char().
>
>  In 7.2 is possible use millisecond / microsecond format:
>
> # select to_char(now()+'2s 324 ms'::interval, 'HH:MM:SS MS');
>    to_char
> --------------
>  10:10:59 324
> (1 row)
>
> # select to_char(now()+'2s 324 ms 10 microsecon'::interval, 'HH:MM:SS US');
>      to_char
> -----------------
>  10:10:03 324010
> (1 row)
>
> 	Karel
>
>

-- 
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere


Thu, 4 Oct 2001 09:56:10 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: Timestamp, fractional seconds problem
Thomas,

Can you explain more how this functionality has changed?  I know that in 
the JDBC driver fractional seconds are assumed to be two decimal places. 
  If this is no longer true, I need to understand the new symantics so 
that the JDBC parsing routines can be changed.  Other interfaces may 
have similar issues.

thanks,
--Barry

Thomas Lockhart wrote:

>>Problem: the external representation of time and timestamp are
>>          less precise than the internal representation.
>>
> 
> Fixed (as of yesterday) in the upcoming release.
> 
>                        - Thomas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 



Thu, 04 Oct 2001 10:36:26 -0700
Barry Lind <barry@xythos.com>
Re: [HACKERS] Timestamp, fractional seconds problem
Hi Thomas,

Could I get some more specific information on how this is fixed.  Keep in mind that using tochar() is not an option for us in that we ned to use COPY to/from the client.

Thanks,

L.

On Thu, 4 Oct 2001, Thomas Lockhart wrote:

> > Problem: the external representation of time and timestamp are
> >           less precise than the internal representation.
>
> Fixed (as of yesterday) in the upcoming release.
>
>                        - Thomas
>

-- 
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere


Thu, 4 Oct 2001 10:46:10 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: Timestamp, fractional seconds problem
Laurette Cisneros wrote:
> 
> Could I get some more specific information on how this is fixed.  Keep in mind that using tochar() is not an option for us in that we ned to use COPY to/from the client.

I'm finishing up implementing SQL99-style precision features in
timestamp et al, so there will no longer be an arbitrary rounding of
time to 2 decimal places when values are output. There will of course be
*other* issues for you to worry about, since the default precision
specified by SQL99 is zero decimal places...

                      - Thomas

Thu, 04 Oct 2001 18:00:22 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Timestamp, fractional seconds problem
Thanks Thomas...at least there will be a way to specify more than 2.  we are looking forward to this release...

L.
On Thu, 4 Oct 2001, Thomas Lockhart wrote:

> Laurette Cisneros wrote:
> >
> > Could I get some more specific information on how this is fixed.  Keep in mind that using tochar() is not an option for us in that we ned to use COPY to/from the client.
>
> I'm finishing up implementing SQL99-style precision features in
> timestamp et al, so there will no longer be an arbitrary rounding of
> time to 2 decimal places when values are output. There will of course be
> *other* issues for you to worry about, since the default precision
> specified by SQL99 is zero decimal places...
>
>                       - Thomas
>

-- 
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere


Thu, 4 Oct 2001 11:59:44 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: Timestamp, fractional seconds problem
> Can you explain more how this functionality has changed?  I know that in
> the JDBC driver fractional seconds are assumed to be two decimal places.
>   If this is no longer true, I need to understand the new symantics so
> that the JDBC parsing routines can be changed.  Other interfaces may
> have similar issues.

OK. (Remember that the new behaviors can be changed if this doesn't work
for you).

Formerly, all times had either zero or two fractional decimal places.
Now, times are explicitly truncated to their defined precision at a few
specific points in processing (e.g. when reading a literal constant or
when storing into a column). At all other points in processing, the
values are allowed to take on whatever fractional digits might have come
from math operations or whatever.

The output routines now write the maximum number of fractional digits
reasonably present for a floating point number (10 for time, should be
but isn't less for timestamp) and then trailing zeros are hacked out,
two digits at a time.

The regression tests produced basically the same results as always, once
the time and timestamp columns were defined to be "time(2)" or
"timestamp(2)".

But there is definitely the possibility of more precision than before in
the output string for time fields.

                       - Thomas

Thu, 04 Oct 2001 20:13:17 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: [HACKERS] Timestamp, fractional seconds problem
Thomas Lockhart <lockhart@fourpalms.org> writes:
> ... then trailing zeros are hacked out,
> two digits at a time.

I was wondering why it seemed to always want to produce an even number
of fractional digits.  Why are you doing it 2 at a time and not 1?
I should think timestamp(1) would produce 1 fractional digit, not
two digits of which the second is always 0 ...

			regards, tom lane

Thu, 04 Oct 2001 16:48:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Timestamp, fractional seconds problem 
> > ... then trailing zeros are hacked out,
> > two digits at a time.
> I was wondering why it seemed to always want to produce an even number
> of fractional digits.  Why are you doing it 2 at a time and not 1?
> I should think timestamp(1) would produce 1 fractional digit, not
> two digits of which the second is always 0 ...

Hmm. Good point wrt timestamp(1). I hack out two digits at a time to get
convergence on a behavior consistant with previous releases of having
(at least) two digits of precision (not one or three). I was trying to
minimize the impact of the other changes.

Note that another "arbitrary difference" is that, by default, TIMESTAMP
is actually TIMESTAMP WITH TIME ZONE. SQL99 specifies otherwise, but
there would seem to be fewer porting and upgrade issues for 7.2 if we
choose the current behavior.

Not sure where pg_dump and other utilities gin up the SQL9x type names,
but we should fix things during beta to be consistant.

                      - Thomas

Thu, 04 Oct 2001 21:02:02 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Timestamp, fractional seconds problem
Thomas Lockhart <lockhart@fourpalms.org> writes:
> Not sure where pg_dump and other utilities gin up the SQL9x type names,
> but we should fix things during beta to be consistant.

I believe pg_dump and psql are already okay now that I fixed
format_type.  Not sure if there are dependencies in other utilities.

			regards, tom lane

Thu, 04 Oct 2001 17:32:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Timestamp, fractional seconds problem 