Thread: Timestamp, fractional seconds problem

Timestamp, fractional seconds problem

From
Laurette Cisneros
Date:
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




Re: Timestamp, fractional seconds problem

From
Thomas Lockhart
Date:
> Problem: the external representation of time and timestamp are
>           less precise than the internal representation.

Fixed (as of yesterday) in the upcoming release.
                      - Thomas


Re: Timestamp, fractional seconds problem

From
Karel Zak
Date:
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


Re: Timestamp, fractional seconds problem

From
Laurette Cisneros
Date:
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



Re: Timestamp, fractional seconds problem

From
Laurette Cisneros
Date:
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



Re: Timestamp, fractional seconds problem

From
Thomas Lockhart
Date:
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


Re: Timestamp, fractional seconds problem

From
Barry Lind
Date:
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)
>
>



Re: Timestamp, fractional seconds problem

From
Laurette Cisneros
Date:
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



Re: Timestamp, fractional seconds problem

From
Thomas Lockhart
Date:
> 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

Re: Timestamp, fractional seconds problem

From
Tom Lane
Date:
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


Re: Timestamp, fractional seconds problem

From
Thomas Lockhart
Date:
> > ... 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


Re: Timestamp, fractional seconds problem

From
Tom Lane
Date:
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


Re: Timestamp, fractional seconds problem

From
"Zeugswetter Andreas SB SD"
Date:
> > ... 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