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 98B469DC85C for ; Wed, 1 Mar 2006 16:55:55 -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 89697-06 for ; Wed, 1 Mar 2006 16:55:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9C54F9DCCF1 for ; Wed, 1 Mar 2006 16:55:52 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k21KtL84015151; Wed, 1 Mar 2006 15:55:21 -0500 (EST) To: Mark Dilger cc: Bruce Momjian , pgsql-hackers@postgresql.org Subject: Re: [SQL] Interval subtracting In-reply-to: <44060682.9020505@markdilger.com> References: <200603012013.k21KDFM21139@candle.pha.pa.us> <14834.1141244891@sss.pgh.pa.us> <44060682.9020505@markdilger.com> Comments: In-reply-to Mark Dilger message dated "Wed, 01 Mar 2006 12:39:30 -0800" Date: Wed, 01 Mar 2006 15:55:21 -0500 Message-ID: <15150.1141246521@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.111 required=5 tests=[AWL=0.111] X-Spam-Score: 0.111 X-Spam-Level: X-Archive-Number: 200603/61 X-Sequence-Number: 80459 Mark Dilger writes: > Tom Lane wrote: >> I guess I would expect a good result to satisfy one of these three >> cases: >> * month > 0 and 0 <= day < 30 >> * month < 0 and -30 < day <= 0 >> * month = 0 and -30 < day < 30 >> If you believe that then "1 month -95 days" should justify to >> "-2 months -5 days". > How would you expect justify_hours to behave? I extrapolate from your rules > above that: > * month > 0 and 0 <= day < 30 and 0 <= hours < 24 > * month < 0 and -30 < day <= 0 and -24 < hours <= 0 > * month = 0 and -30 < day <= 0 and -24 < hours <= 0 > * month = 0 and 0 <= day < 30 and 0 <= hours < 24 Hmmm ... I think it would be better if the two functions were independent, if possible. Your spec above implies that justify_hours implicitly does justify_days as well, which seems a bit restrictive. Furthermore, justify_hours should only assume that 1 day == 24 hours, which while broken by DST is still a lot solider assumption than justify_days' 1 month == 30 days. I can well believe that a lot of people only want to make the first assumption. So I'm inclined to think that justify_hours is responsible for reducing the seconds part to less-than-24-hours and pushing any overflow into the days part (but not touching months), while justify_days is responsible for reducing the days part to less-than-30-days and pushing any overflow into the months part (but not touching seconds). If you want both you apply both functions, probably in that order. (I wonder if there are any cases where applying justify_days before justify_hours would be useful. Offhand I can't see one ...) regards, tom lane