Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f76FXi631977 for ; Mon, 6 Aug 2001 11:33:44 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f76FXaY21807; Mon, 6 Aug 2001 11:33:36 -0400 (EDT) To: "Josh Berkus" cc: Peter Eisentraut , pgsql-sql@postgresql.org Subject: Re: Re: Data type confusion In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Mon, 06 Aug 2001 08:08:19 -0700" Date: Mon, 06 Aug 2001 11:33:36 -0400 Message-ID: <21804.997112016@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200108/60 X-Sequence-Number: 4281 "Josh Berkus" writes: > Peter is absolutely correct here. '1 year'::INTERVAL - '1 > day'::INTERVAL is '364 days'::INTERVAL most of the time. However, on > leap years it is '365 days'. Au contraire, it is always '1 year - 1 day'::INTERVAL. That is a two-part interval value and is not reduced further. When you add it to a date or timestamp, *then* you find out how many days are meant. > One way to simplify this would be not to allow any division operations > on INTERVALS that result in a modulo of a smaller increment than the > INTERVAL value expressed. Thus, one could "'3 months'::INTERVAL / 3" > but would not be allowed to "'2 months::INTERVAL / 3". However, this > seems kind of unfair to hour, minute, and second values whose fractions > are well-defined and easily manipulated. I was toying with the notion of allowing scalings whose results didn't introduce any fractional part to the "months" field. For example '2 months + 1 day' / 2.0 = '1 month + 12hrs' '3 months + 1 day' / 2.0 = error (can't have a half month) '61 days' / 2.0 = '30 days 12hrs' However, I fear that this would make no sense to anyone who hadn't thought about the issues as carefully as we have in this thread. > Or, to put it another way, 95% of the time users just want to do simple > things. Like we want to know how many weeks an employee has been with > us for: '2 years 3 months'::INTERVAL / '1 week'::INTERVAL (and we > don't care about the fractional week left over). Good point. Ugly as the "30 day" convention is, it is probably close enough for that sort of thing. regards, tom lane