Re: casting interval to time

Antti Haapala <antti.haapala@iki.fi> writes:
> It's also Debian (3.0).

On investigation the interval_time() function was completely broken for
the --enable-integer-datetimes case --- it was reducing the interval
value modulo one second, rather than modulo one day as intended.
I also noticed that neither the integer nor float case behaved
rationally for negative intervals.  I've applied the attached patch
to 7.3 and HEAD ...
        regards, tom lane

*** src/backend/utils/adt/date.c.orig    Thu Feb 13 12:04:24 2003
--- src/backend/utils/adt/date.c    Mon Jun 16 14:56:53 2003
***************
*** 999,1004 ****
--- 999,1009 ----  /* interval_time()  * Convert interval to time data type.
+  *
+  * This is defined as producing the fractional-day portion of the interval.
+  * Therefore, we can just ignore the months field.  It is not real clear
+  * what to do with negative intervals, but we choose to subtract the floor,
+  * so that, say, '-2 hours' becomes '22:00:00'.  */ Datum interval_time(PG_FUNCTION_ARGS)
***************
*** 1007,1021 ****     TimeADT        result;  #ifdef HAVE_INT64_TIMESTAMP     result = span->time;
!     if ((result >= INT64CONST(86400000000))
!         || (result <= INT64CONST(-86400000000)))
!         result -= (result / INT64CONST(1000000) * INT64CONST(1000000)); #else
-     Interval    span1;
-      result = span->time;
!     TMODULO(result, span1.time, 86400e0); #endif      PG_RETURN_TIMEADT(result);
--- 1012,1034 ----     TimeADT        result;  #ifdef HAVE_INT64_TIMESTAMP
+     int64        days;
+      result = span->time;
!     if (result >= INT64CONST(86400000000))
!     {
!         days = result / INT64CONST(86400000000);
!         result -= days * INT64CONST(86400000000);
!     }
!     else if (result < 0)
!     {
!         days = (-result + INT64CONST(86400000000-1)) / INT64CONST(86400000000);
!         result += days * INT64CONST(86400000000);
!     } #else     result = span->time;
!     if (result >= 86400e0 || result < 0)
!         result -= floor(result / 86400e0) * 86400e0; #endif      PG_RETURN_TIMEADT(result);