X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5DEF89DCAB9 for ; Wed, 1 Mar 2006 18:30:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23664-09 for ; Wed, 1 Mar 2006 18:30:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 9643F9DC821 for ; Wed, 1 Mar 2006 18:30:46 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 75DBC30C3A; Wed, 1 Mar 2006 23:30:48 +0100 (MET) From: Mark Dilger X-Newsgroups: pgsql.hackers Subject: Re: [SQL] Interval subtracting Date: Wed, 01 Mar 2006 14:30:11 -0800 Organization: Hub.Org Networking Services Lines: 253 Message-ID: <44062073.9010803@markdilger.com> References: <44061060.8080407@markdilger.com> <200603012145.k21LjRX11928@candle.pha.pa.us> <44061790.1040302@markdilger.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="------------050000040104080701000805" X-Complaints-To: usenet@news.hub.org Cc: Bruce Momjian To: Mark Dilger User-Agent: Mozilla Thunderbird 1.0 (X11/20050130) X-Accept-Language: en-us, en In-Reply-To: <44061790.1040302@markdilger.com> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-hackers@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.094 required=5 tests=[AWL=0.094] X-Spam-Score: 0.094 X-Spam-Level: X-Archive-Number: 200603/73 X-Sequence-Number: 80471 This is a multi-part message in MIME format. --------------050000040104080701000805 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit A new patch is attached. Please note the regression differences. mark --------------050000040104080701000805 Content-Type: text/plain; name="patch2" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="patch2" Index: src/backend/utils/adt/timestamp.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.160 diff --context=5 -r1.160 timestamp.c *** src/backend/utils/adt/timestamp.c 22 Nov 2005 22:30:33 -0000 1.160 --- src/backend/utils/adt/timestamp.c 1 Mar 2006 22:29:32 -0000 *************** *** 2003,2013 **** TMODULO(result->time, wholeday, USECS_PER_DAY); #else TMODULO(result->time, wholeday, (double) SECS_PER_DAY); #endif result->day += wholeday; /* could overflow... */ ! PG_RETURN_INTERVAL_P(result); } /* * interval_justify_days() --- 2003,2024 ---- TMODULO(result->time, wholeday, USECS_PER_DAY); #else TMODULO(result->time, wholeday, (double) SECS_PER_DAY); #endif result->day += wholeday; /* could overflow... */ ! if ((result->time < 0) && ! ((result->month >= 0 && result->day >= 0) || ! (result->month > 0 && result->day < 0 && (-1.0 * (double)result->day)/((double)result->month) < ((double)DAYS_PER_MONTH)) || ! (result->month < 0 && result->day > 0 && ((double)result->day)/(-1.0 * (double)result->month) > ((double)DAYS_PER_MONTH)))) ! { ! #ifdef HAVE_INT64_TIMESTAMP ! result->time += USECS_PER_DAY; ! #else ! result->time += (double) SECS_PER_DAY; ! #endif ! result->day--; ! } PG_RETURN_INTERVAL_P(result); } /* * interval_justify_days() *************** *** 2028,2037 **** --- 2039,2053 ---- result->time = span->time; wholemonth = result->day / DAYS_PER_MONTH; result->day -= wholemonth * DAYS_PER_MONTH; result->month += wholemonth; + if (result->day < 0 && result->month > 0) + { + result->day += DAYS_PER_MONTH; + result->month--; + } PG_RETURN_INTERVAL_P(result); } /* timestamp_pl_interval() --------------050000040104080701000805 Content-Type: text/plain; name="regression.diffs2" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="regression.diffs2" *** ./expected/timestamp.out Sat Jun 25 20:04:18 2005 --- ./results/timestamp.out Wed Mar 1 14:26:33 2006 *************** *** 488,494 **** | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 6 hours 27 mins 59 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec --- 488,494 ---- | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 1 day -17 hours -32 mins -1 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec *************** *** 557,563 **** | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 6 hours 27 mins 59 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec --- 557,563 ---- | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 1 day -17 hours -32 mins -1 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec ====================================================================== *** ./expected/timestamptz.out Sat Jun 25 20:04:18 2005 --- ./results/timestamptz.out Wed Mar 1 14:26:34 2006 *************** *** 483,489 **** | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 6 hours 27 mins 59 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec --- 483,489 ---- | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 1 day -17 hours -32 mins -1 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec *************** *** 551,557 **** | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 6 hours 27 mins 59 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec --- 551,557 ---- | @ 306 days 6 hours 27 mins 59 secs ago | @ 2 days 6 hours 27 mins 59 secs ago | @ 1 day 6 hours 27 mins 59 secs ago ! | @ 1 day -17 hours -32 mins -1 secs ago | @ 57 days 17 hours 32 mins 1 sec | @ 58 days 17 hours 32 mins 1 sec | @ 362 days 17 hours 32 mins 1 sec ====================================================================== *** ./expected/horology.out Thu Sep 8 09:49:04 2005 --- ./results/horology.out Wed Mar 1 14:26:36 2006 *************** *** 2115,2124 **** | Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs | Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0 ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 59 mins 59 secs ago ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 59 mins 56 secs ago ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 9 hours 59 mins 58 secs ago ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 10 hours 59 mins 57 secs ago | Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago | Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago | Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago --- 2115,2124 ---- | Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs | Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0 ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 1 day -23 hours -1 secs ago ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -18 hours -4 secs ago ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -14 hours -2 secs ago ! | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -13 hours -3 secs ago | Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago | Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago | Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago *************** *** 2132,2140 **** | Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0 ! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 59 mins 57 secs ago ! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 8 hours 59 mins 59 secs ago ! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 9 hours 59 mins 58 secs ago | Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago | Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago | Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago --- 2132,2140 ---- | Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0 ! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -19 hours -3 secs ago ! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -15 hours -1 secs ago ! | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -14 hours -2 secs ago | Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago | Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago | Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago *************** *** 2149,2156 **** | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0 ! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 4 hours 2 secs ago ! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 5 hours 1 sec ago | Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago | Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago | Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago --- 2149,2156 ---- | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0 ! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -19 hours -59 mins -58 secs ago ! | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -18 hours -59 mins -59 secs ago | Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago | Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago | Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago *************** *** 2166,2172 **** | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0 ! | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 59 mins 59 secs ago | Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago | Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago | Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago --- 2166,2172 ---- | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0 ! | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -23 hours -1 secs ago | Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago | Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago | Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago ====================================================================== --------------050000040104080701000805--