round - timestamp bug

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Gonzalo Arana
Your email address : garana(at)sinectis(dot)com

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium III

Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.12-20 ELF
(libc-2.1.2)

PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.3

Compiler used (example: gcc 2.8.0) : egcs-2.91.66

Please enter a FULL description of your problem:
------------------------------------------------

It seems that there is a problem when retrieving a timestamp value (rounding).

NO minute has 61 seconds. Am I wrong?

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

radius=# create table x (x timestamp);
CREATE
radius=# insert into x (x) values ('Tue 23 Jan 21:38:59.997 2001');
INSERT 619178 1
radius=# select * from x;
x
---------------------------------
Tue 23 Jan 21:38:60.00 2001 ART
(1 row)

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Ugly patch to scripts:

radius=# select to_char(x,'Dy DD Mon HH24:MI:SS YYYY') from x;
to_char
--------------------------
Tue 23 Jan 21:38:59 2001
(1 row)

Of course, you'll lose the fraction of seconds otherwise are available.

On Wed, Jan 24, 2001 at 05:50:24PM -0300,
Gonzalo Arana <garana(at)sinectis(dot)com(dot)ar> wrote:
>
> It seems that there is a problem when retrieving a timestamp value (rounding).
>
> NO minute has 61 seconds. Am I wrong?

When leap seconds occur, minutes can have 61 seconds.

> radius=# create table x (x timestamp);
> CREATE
> radius=# insert into x (x) values ('Tue 23 Jan 21:38:59.997 2001');
> INSERT 619178 1
> radius=# select * from x;
> x
> ---------------------------------
> Tue 23 Jan 21:38:60.00 2001 ART
> (1 row)
>
> radius=# select to_char(x,'Dy DD Mon HH24:MI:SS YYYY') from x;
> to_char
> --------------------------
> Tue 23 Jan 21:38:59 2001
> (1 row)

The to_char() is directly based on 'tm' struct and in current version
not use 'fsec' from timestamp2tm(). Hmm... I add it to my TODO.

I not sure if I fix it for 7.1, may be as late as in 7.1.1 :-(

It is not fatal bug it is almost feature that SS return directly sec :-)

Karel

Gonzalo Arana wrote:
>
> ============================================================================
> POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
> Your name : Gonzalo Arana
> Your email address : garana(at)sinectis(dot)com
>
> System Configuration
> ---------------------
> Architecture (example: Intel Pentium) : Intel Pentium III
>
> Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.12-20 ELF
> (libc-2.1.2)
>
> PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.3
>
> Compiler used (example: gcc 2.8.0) : egcs-2.91.66
>
> Please enter a FULL description of your problem:
> ------------------------------------------------
>
> It seems that there is a problem when retrieving a timestamp value (rounding).
>
> NO minute has 61 seconds. Am I wrong?
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>
> radius=# create table x (x timestamp);
> CREATE
> radius=# insert into x (x) values ('Tue 23 Jan 21:38:59.997 2001');
> INSERT 619178 1
> radius=# select * from x;
> x
> ---------------------------------
> Tue 23 Jan 21:38:60.00 2001 ART
> (1 row)
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
>
> Ugly patch to scripts:
>
> radius=# select to_char(x,'Dy DD Mon HH24:MI:SS YYYY') from x;
> to_char
> --------------------------
> Tue 23 Jan 21:38:59 2001
> (1 row)
>
> Of course, you'll lose the fraction of seconds otherwise are available.

Actually, such leap-seconds are possible. This can happen when your
timebase is a NTP time server such as the Naval Observatory, etc. They
are used for micro adjustments to adjust clock to siderial (celestial)
time. I have had to write date+time classes in C++ which could handle
this exact situation... X-). The fact that Postgres-SQL can handle this
is probably a good thing.

-Bill Boyle

> Gonzalo Arana wrote:
>> radius=# create table x (x timestamp);
>> CREATE
>> radius=# insert into x (x) values ('Tue 23 Jan 21:38:59.997 2001');
>> INSERT 619178 1
>> radius=# select * from x;
>> x
>> ---------------------------------
>> Tue 23 Jan 21:38:60.00 2001 ART
>> (1 row)

This is just a display artifact. The value stored is actually ... 59.997
(plus or minus a little bit from floating-point roundoff error) but the
seconds value is rounded to two digits during display.

I have suggested in the past that it'd be better to round the floating
value to two fractional digits before we break it down to date/hh/mm/ss,
rather than after, but that suggestion seems to have fallen on deaf
ears.

regards, tom lane

> > It seems that there is a problem when retrieving a timestamp value (rounding).
> > NO minute has 61 seconds. Am I wrong?
> > radius=# select timestamp 'Tue 23 Jan 21:38:59.997 2001';
> > ---------------------------------
> > Tue 23 Jan 21:38:60.00 2001 ART
> Actually, such leap-seconds are possible. This can happen when your
> timebase is a NTP time server such as the Naval Observatory, etc. They
> are used for micro adjustments to adjust clock to siderial (celestial)
> time. I have had to write date+time classes in C++ which could handle
> this exact situation... X-). The fact that Postgres-SQL can handle this
> is probably a good thing.

All true, but the underlying problem in this case is not that "59.997"
or even "60.0" is accepted, but that it is displayed as "60.0" (although
a value of "60" does show up during leap second transitions, it is only
as a placeholder while waiting for the next "official minute" to start
;). The original report did not give complete platform details, but in
my recollection the *only* recent cases of this display problem come
from Mandrake systems which are built with overly aggressive compiler
optimization options. Check and verify that you are not using "-O n" and
"-fast-math" together when compiling PostgreSQL.

As an aside, the Mandrake folks are aware of this problem in their
distro and have recently fixed their version of the spec file; hopefully
we will get this folded back into Lamar's spec file before 7.1 goes out.

- Thomas

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> The original report did not give complete platform details, but in
> my recollection the *only* recent cases of this display problem come
> from Mandrake systems which are built with overly aggressive compiler
> optimization options.

No, the behavior is not platform-specific. I'm on HP-PA:

regression=# select 'Tue 23 Jan 21:38:59.997 2001'::timestamp;
?column?
---------------------------
2001-01-23 21:38:60.00-05
(1 row)

The problem is that we round the fractional seconds part to two digits
only after we've separated seconds from the other fields. (I imagine
the code is not even doing that explicitly, but leaving it to sprintf
to do so.) It would work better if we rounded the entire floating
timestamp value to two fractional digits before we break it down,
eg with

tstamp = rint(tstamp * 100.0) / 100.0;

regards, tom lane

> No, the behavior is not platform-specific. I'm on HP-PA:

Hmm. Don't see that on my Linux box :(

We don't have regression tests which cover this case?

> The problem is that we round the fractional seconds part to two digits
> only after we've separated seconds from the other fields. (I imagine
> the code is not even doing that explicitly, but leaving it to sprintf
> to do so.) It would work better if we rounded the entire floating
> timestamp value to two fractional digits before we break it down,
> eg with
> tstamp = rint(tstamp * 100.0) / 100.0;

Sure, that's a possibility. There is already a macro to help do that
sort of thing, but I've not jumped to this solution since we probably
should allow some kind of variable precision on date/time types.

- Thomas