Thread: BUG #1609: Bug in interval datatype for 64 Bit timestamps

BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
"Oliver Siegmar"
Date:
The following bug has been logged online:

Bug reference:      1609
Logged by:          Oliver Siegmar
Email address:      oliver@siegmar.net
PostgreSQL version: 8.0.2
Operating system:   Linux
Description:        Bug in interval datatype for 64 Bit timestamps
Details:

Postgres compiled with --enable-integer-datetimes has a bug in interval
datatype representation -

select '10.10 secs ago'::interval;

     interval
-------------------
 @ 10.-10 secs ago
(1 row)


Without --enable-integer-datetimes -

     interval
------------------
 @ 10.10 secs ago
(1 row)


Please CC me, because I'm not on the list.


Oliver

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Tom Lane
Date:
"Oliver Siegmar" <oliver@siegmar.net> writes:
> select '10.10 secs ago'::interval;

>      interval
> -------------------
>  @ 10.-10 secs ago
> (1 row)

What datestyle are you using?

            regards, tom lane

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Tom Lane
Date:
Oliver Siegmar <oliver@siegmar.net> writes:
>> What datestyle are you using?

> Non-ISO (Postgres in that case), but the handling for non-ISO is all the same
> in interval.c ...

Yeah, I just confirmed here that it's broken the same way in all three
non-ISO datestyles.  Will look into a fix later today.

            regards, tom lane

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Tom Lane
Date:
I've applied this patch.

            regards, tom lane

Index: datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.137
diff -c -r1.137 datetime.c
*** datetime.c    11 Jan 2005 18:33:45 -0000    1.137
--- datetime.c    20 Apr 2005 17:09:57 -0000
***************
*** 3883,3899 ****
              /* fractional seconds? */
              if (fsec != 0)
              {
  #ifdef HAVE_INT64_TIMESTAMP
                  if (is_before || ((!is_nonzero) && (tm->tm_sec < 0)))
                      tm->tm_sec = -tm->tm_sec;
                  sprintf(cp, "%s%d.%02d secs", (is_nonzero ? " " : ""),
!                         tm->tm_sec, (((int) fsec) / 10000));
                  cp += strlen(cp);
-                 if (!is_nonzero)
-                     is_before = (fsec < 0);
  #else
-                 fsec_t        sec;
-
                  fsec += tm->tm_sec;
                  sec = fsec;
                  if (is_before || ((!is_nonzero) && (fsec < 0)))
--- 3883,3907 ----
              /* fractional seconds? */
              if (fsec != 0)
              {
+                 fsec_t        sec;
+
  #ifdef HAVE_INT64_TIMESTAMP
+                 sec = fsec;
                  if (is_before || ((!is_nonzero) && (tm->tm_sec < 0)))
+                 {
                      tm->tm_sec = -tm->tm_sec;
+                     sec = -sec;
+                     is_before = TRUE;
+                 }
+                 else if ((!is_nonzero) && (tm->tm_sec == 0) && (fsec < 0))
+                 {
+                     sec = -sec;
+                     is_before = TRUE;
+                 }
                  sprintf(cp, "%s%d.%02d secs", (is_nonzero ? " " : ""),
!                         tm->tm_sec, (((int) sec) / 10000));
                  cp += strlen(cp);
  #else
                  fsec += tm->tm_sec;
                  sec = fsec;
                  if (is_before || ((!is_nonzero) && (fsec < 0)))
***************
*** 3905,3913 ****
                      is_before = (fsec < 0);
  #endif
                  is_nonzero = TRUE;
-
-                 /* otherwise, integer seconds only? */
              }
              else if (tm->tm_sec != 0)
              {
                  int            sec = tm->tm_sec;
--- 3913,3920 ----
                      is_before = (fsec < 0);
  #endif
                  is_nonzero = TRUE;
              }
+             /* otherwise, integer seconds only? */
              else if (tm->tm_sec != 0)
              {
                  int            sec = tm->tm_sec;

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Oliver Siegmar
Date:
Hi Tom,

On Wednesday 20 April 2005 17:57, Tom Lane wrote:
> "Oliver Siegmar" <oliver@siegmar.net> writes:
> > select '10.10 secs ago'::interval;
> >
> >      interval
> > -------------------
> >  @ 10.-10 secs ago
> > (1 row)
>
> What datestyle are you using?

Non-ISO (Postgres in that case), but the handling for non-ISO is all the same
in interval.c ...


ISO works fine -

   interval
--------------
 -00:00:10.10
(1 row)



Oliver

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Oliver Siegmar
Date:
> I've applied this patch.

It removed the bug, but also added a new one (hopefully only one ;-))


...now with ISO DateStyle -


select '2005 years 4 mons 20 days 15 hours 57 mins 12.1 secs ago'::interval;


Before your patch:

                 interval
-------------------------------------------
 -2005 years -4 mons -20 days -15:57:12.10
(1 row)



after applying your patch:

                     interval
---------------------------------------------------
 -2005 years -4 mons -20 days -15:57:12.1000000001
(1 row)




Oliver

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Tom Lane
Date:
Oliver Siegmar <oliver@siegmar.net> writes:
> It removed the bug, but also added a new one (hopefully only one ;-))

I don't think it's a new bug, seeing that I didn't change the code
for the ISO case.

I see the imprecise result only in the non-integer-datetime case; is
it acting differently for you?

If it is only the float case, some imprecision is to be expected.

            regards, tom lane

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Tom Lane
Date:
Oliver Siegmar <oliver@siegmar.net> writes:
> On Thursday 21 April 2005 15:57, Tom Lane wrote:
>> If it is only the float case, some imprecision is to be expected.

> So everything is okay?

Well, it's not necessarily *wrong*, but maybe we could improve it.
The code currently assumes it can print 10 fractional digits in the
float case, which is overly optimistic once you get a large number
of days in the "days" component.  Maybe we should add some code
to back off the precision depending on the number of days?

            regards, tom lane

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Bruce Momjian
Date:
Tom Lane wrote:
> Oliver Siegmar <oliver@siegmar.net> writes:
> > On Thursday 21 April 2005 15:57, Tom Lane wrote:
> >> If it is only the float case, some imprecision is to be expected.
>
> > So everything is okay?
>
> Well, it's not necessarily *wrong*, but maybe we could improve it.
> The code currently assumes it can print 10 fractional digits in the
> float case, which is overly optimistic once you get a large number
> of days in the "days" component.  Maybe we should add some code
> to back off the precision depending on the number of days?

Well, it seems strange to change the display based on the number of
days, but on the other hand this is how exponential numbers are
displayed, with an X.YYYY EZZ so I suppose it does make sense to
suppress some of the fractional seconds for a large number of days.

I assume we would have to document this behavior.  How do we determine
the range to adjust?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: BUG #1609: Bug in interval datatype for 64 Bit timestamps

From
Oliver Siegmar
Date:
On Thursday 21 April 2005 15:57, Tom Lane wrote:
> I don't think it's a new bug, seeing that I didn't change the code
> for the ISO case.
>
> I see the imprecise result only in the non-integer-datetime case; is
> it acting differently for you?

You're right - it has nothing to do with your patch. I thought that because I
had the problem on one system (with your patch) but not on another (without
your patch). But these systems are having another difference - 64bit integer
datetimes.

I have this imprecise only on the non-integer-datetime server, too.

> If it is only the float case, some imprecision is to be expected.

So everything is okay?


Oliver