Thread: 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 roundthe 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 currenttimestamp 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 typesin datetime.c (called by timestamp_out, time_out) to represent least 6 digits beyond the decimal (ie "%0.6f"). A configurable format wouldalso 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 silentdata integrity bug with unacceptable workarounds. Thanks! Laurette Cisneros (laurette@nextbus.com) Elein Mustain NextBus Information Systems
> Problem: the external representation of time and timestamp are
> less precise than the internal representation.
Fixed (as of yesterday) in the upcoming release.
- Thomas
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
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
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 usin 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
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
forus 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
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) > >
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 forus 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
> 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
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
> > ... 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
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
> > ... 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 ... Yup, same here. I'd also prefer 1 at a time. If you want compatibility, I would do it only for the first 2 digits. Andreas