X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 248D030ECC4 for ; Mon, 16 Jun 2003 19:02:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 71223-05 for ; Mon, 16 Jun 2003 16:02:43 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 5AE3630EB24 for ; Mon, 16 Jun 2003 16:02:43 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h5GJ2cU6005950; Mon, 16 Jun 2003 15:02:38 -0400 (EDT) To: Antti Haapala Cc: Tomasz Myrta , PGSQL-SQL Subject: Re: casting interval to time In-reply-to: References: <3EEDCB67.4090504@klaster.net> <2971.1055775231@sss.pgh.pa.us> <3EEDDB8D.5080401@klaster.net> <3222.1055776631@sss.pgh.pa.us> Comments: In-reply-to Antti Haapala message dated "Mon, 16 Jun 2003 18:56:27 +0300" Date: Mon, 16 Jun 2003 15:02:38 -0400 Message-ID: <5949.1055790158@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200306/160 X-Sequence-Number: 13918 Antti Haapala 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);