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

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

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:

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

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

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

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