On Tue, Jul 27, 2021 at 3:36 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
with c1 as ( select '1 month 1 day 1 second'::interval as i1, '1.234 month 1.234 day 1.234 second'::interval as i3),
c2 as ( select i1*1.234 as i2, i3 from c1)
select i2::text as i2_txt, i3::text from c2 as i3_txt;
It's nice to envision all forms of fancy calculations. But the fact is that
'1.5 month'::interval * 2 != '3 month"::interval
with any of these patches - and if that doesn't work - the rest of the strange numbers really seem to be irrelevant.
If there is a desire to handle fractional cases - then all pieces need to be held as provided until they are transformed into something. In other words - 1.5 month needs to be held as 1.5 month until we ask for it to be reduced to 1 month and 15 days at some point. If the interval data type immediately casts 1.5 months to 1 month 15 days then all subsequent calculations are going to be wrong.
I appreciate there is generally no way to accomplish this right now - but that means walking away from things like 1 month * 1.234 as being not calculable as opposed to trying to piece something together that fails pretty quickly.