On 11/23/21 10:41, Isaac Morland wrote: > Umm, it's definitely negative: > > odyssey=> select '1 month -31 days +12:00:00'::interval < '0 > months'::interval; > ---------- > t
Well, what you've shown here is that it's "negative" according to an arbitrary total ordering imposed in interval_cmp_value for the purpose of making it indexable in a btree ... > It's just that due to the complexities of our calendar/time systems, adding > it to a timestamp can move the timestamp in either direction: ... and this is just another way of saying that said arbitrary choice of btree ordering can't be used to tell you whether the interval is semantically positive or negative. (Of course, for a great many intervals, the two answers will be the same, but they're still answers to different questions.) > I'm working on a patch to add abs(interval) so I noticed this. There are > lots of oddities, including lots of intervals which compare equal to 0 but > which can change a timestamp when added to it, but as presently designed, > this particular interval compares as negative. It's no use—it's oddities all the way down. You can shove them off to one side of the desk or the other depending on your intentions of the moment, but they're always there. If you want to put intervals in a btree, you can define a total ordering where all days are 24 hours and all months are 30 days, and then there are no oddities in your btree, they're just everywhere else. Or you can compare your unknown interval to a known one like '0 months' and say you know whether it's "negative", you just don't know whether it moves a real date forward or back. Or you can see what it does to a real date, but not know whether it would precede or follow some other interval in a btree. Regards, -Chap
