On Aug 30, 2006, at 7:12 , Bruce Momjian wrote:

Here are the results using my newest patch:

        test=> select interval '41 mon 12 days 360:00' / 10 as quotient_a
             , interval '41 mon -12 days -360:00' / 10 as quotient_b
             , interval '-41 mon 12 days 360:00' / 10 as quotient_c
             , interval '-41 mon -12 days -360:00' / 10 as quotient_d;
quotient_a | quotient_b | quotient_c | quotient_d ------------------------+------------------------- +---------------------------+--------------------------- 4 mons 4 days 40:48:00 | 4 mons 2 days -40:48:00 | -4 mons -2 days +40:48:00 | -4 mons -4 days -40:48:00
        (1 row)
        
        test=> select interval '41 mon 12 days 360:00' * 0.3 as product_a
             , interval '41 mon -12 days -360:00' * 0.3 as product_b
             , interval '-41 mon 12 days 360:00' * 0.3 as product_c
             , interval '-41 mon -12 days -360:00' * 0.3 as product_d;
product_a | product_b | product_c | product_d --------------------------+-------------------------- +-----------------------------+------------------------------ 1 year 12 days 122:24:00 | 1 year 6 days -122:24:00 | -1 years -6 days +122:24:00 | -1 years -12 days -122:24:00
        (1 row)

I see no "23:60" entries.

Using Bruce's newest patch, I still get the "23:60" entries on my machine (no integer-datetimes)

select version();
version ------------------------------------------------------------------------ ------------------------------------------------------------------------ - PostgreSQL 8.2devel on powerpc-apple-darwin8.7.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5341)
(1 row)

select interval '41 mon 12 days 360:00' / 10 as quotient_a
    , interval '41 mon -12 days -360:00' / 10 as quotient_b
    , interval '-41 mon 12 days 360:00' / 10 as quotient_c
    , interval '-41 mon -12 days -360:00' / 10 as quotient_d;
quotient_a | quotient_b | quotient_c | quotient_d ------------------------+------------------------- +---------------------------+--------------------------- 4 mons 4 days 40:48:00 | 4 mons 2 days -40:48:00 | -4 mons -2 days +40:48:00 | -4 mons -4 days -40:48:00
(1 row)

select interval '41 mon 12 days 360:00' * 0.3 as product_a
    , interval '41 mon -12 days -360:00' * 0.3 as product_b
    , interval '-41 mon 12 days 360:00' * 0.3 as product_c
    , interval '-41 mon -12 days -360:00' * 0.3 as product_d;
product_a | product_b | product_c | product_d --------------------------+----------------------------- +-----------------------------+--------------------------------- 1 year 12 days 122:24:00 | 1 year 6 days -122:23:60.00 | -1 years -6 days +122:24:00 | -1 years -12 days -122:23:60.00
(1 row)


The code assume if it is within 0.000001 of a whole number, it should be
rounded to a whole number. Patch attached with comments added.

        /* fractional months full days into days */
        month_remainder_days = month_remainder * DAYS_PER_MONTH;
+       /*
+        *      The remainders suffer from float rounding, so if they are
+        *      within 0.000001 of an integer, we round them to integers.
+        */
+       if (month_remainder_days != (int32)month_remainder_days &&
+               TSROUND(month_remainder_days) == rint(month_remainder_days))
+               month_remainder_days = rint(month_remainder_days);
        result->day += (int32) month_remainder_days;


Don't we want to be checking for rounding at the usec level rather than 0.000001 of a day? I think this should be

        if (month_remainder_days != (int32)month_remainder_days &&
                TSROUND(month_remainder_days * SECS_PER_DAY) ==
                                                                        
rint(month_remainder_days * SECS_PER_DAY))
                month_remainder_days = rint(month_remainder_days);

Another question I have concerns the month_remainder_days != (int32) month_remainder_days comparison. If I understand it correctly, if the TSROUND == rint portion is true, the first part is true. Or is this just a quick, fast check to see if it's necessary to do a more computationally intensive check?

TSROUND isn't defined for HAVE_INT64_TIMESTAMP. My first attempt at performing a corresponding comparison doesn't work:

+       if (month_remainder_days != (int32) month_remainder_days &&
+ #ifdef HAVE_INT64_TIMESTAMP
+               rint(month_remainder_days * USECS_PER_DAY) ==
+                  (month_remainder_days * USECS_PER_DAY))
+ #else
+               TSROUND(month_remainder_days * SECS_PER_DAY) ==
+                  rint(month_remainder_days * SECS_PER_DAY))
+ #endif
+               month_remainder_days = rint(month_remainder_days);

Anyway, I'll pound on this some more tonight.

Michael Glaesemann
grzm seespotcode net

----------------------------------------

Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.165
diff -c -r1.165 timestamp.c
*** src/backend/utils/adt/timestamp.c   13 Jul 2006 16:49:16 -0000      1.165
--- src/backend/utils/adt/timestamp.c   30 Aug 2006 00:48:37 -0000
***************
*** 2518,2523 ****
--- 2518,2536 ----

        /* fractional months full days into days */
        month_remainder_days = month_remainder * DAYS_PER_MONTH;
+       /*
+        *      The remainders suffer from float rounding, so if they are
+        *      within 0.000001 of an integer, we round them to integers.
+        */
+       if (month_remainder_days != (int32) month_remainder_days &&
+ #ifdef HAVE_INT64_TIMESTAMP
+               rint(month_remainder_days * USECS_PER_DAY) ==
+                  (month_remainder_days * USECS_PER_DAY))
+ #else
+               TSROUND(month_remainder_days * SECS_PER_DAY) ==
+                  rint(month_remainder_days * SECS_PER_DAY))
+ #endif
+               month_remainder_days = rint(month_remainder_days);
        result->day += (int32) month_remainder_days;
        /* fractional months partial days into time */
day_remainder += month_remainder_days - (int32) month_remainder_days;
***************
*** 2571,2576 ****
--- 2584,2602 ----

        /* fractional months full days into days */
        month_remainder_days = month_remainder * DAYS_PER_MONTH;
+       /*
+        *      The remainders suffer from float rounding, so if they are
+        *      within 0.000001 of an integer, we round them to integers.
+        */
+       if (month_remainder_days != (int32) month_remainder_days &&
+ #ifdef HAVE_INT64_TIMESTAMP
+               rint(month_remainder_days * USECS_PER_DAY) ==
+                  (month_remainder_days * USECS_PER_DAY))
+ #else
+               TSROUND(month_remainder_days * SECS_PER_DAY) ==
+                  rint(month_remainder_days * SECS_PER_DAY))
+ #endif
+               month_remainder_days = rint(month_remainder_days);
        result->day += (int32) month_remainder_days;
        /* fractional months partial days into time */
day_remainder += month_remainder_days - (int32) month_remainder_days;


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to