On Tue, Jul 27, 2021 at 4:02 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > What I think we have consensus on is that interval_in is doing the > wrong thing in a particular corner case. I have heard nobody but > you suggesting that we should start undertaking behavioral changes > in other interval functions, and I don't believe that that's a good > road to start going down. These behaviors have stood for many years. > Moreover, since the whole thing is by definition operating with > inadequate information, it is inevitable that for every case you > make better there will be another one you make worse.
I agree that we need to be really conservative here. I think Tom is right that if we start changing behaviors that "seem wrong," we will probably make some things better and other things worse. The overall amount of stuff that "seems wrong" will probably not go down, but a lot of people's applications will break when they try to upgrade to v15. That's not going to be a win overall. I think a lot of the discussion on this thread consists of people hoping for things that are not very realistic. The interval type represents the number of months as an integer, and the number of days as an integer. That means that an interval like '0.7 months' does not really exist. If you ask for that interval what you get is actually 21 days, which is a reasonable approximation of 0.7 months but not the same thing, except in April, June, September, and November. So when you then say that you want 0.7 months + 0.3 months to equal 1.0 months, what you're really requesting is that 21 days + 9 days = 1 month. That system has been tried in the past, but it was abandoned roughly around the time of Julius Caeser for the very good reason that the orbital period of the earth about the sun is noticeably greater than 360 days. It would be entirely possible to design a data type that could represent such values more exactly. A data type that had a representation similar to interval but with double values for the numbers of years and months would be able to compute 0.7 months + 0.3 months and get 1.0 months with no problem. If we were doing this over again, I would argue that, with this on-disk representation, 0.7 months ought to be rejected as invalid input, because it's generally not a good idea to have a data type that silently converts a value into a different value that is not equivalent for all purposes. It is confusing and causes people to expect behavior different from what they will actually get. Now, it seems far too late to consider such a change at this point ... and it is also no good considering a change to the on-disk representation of the existing data type at this point ... but it is also no good pretending like we have a floating-point representation of months and days when we actually do not. -- Robert Haas EDB: http://www.enterprisedb.com
