Re: Add operator for dividing interval by an interval

Hi,

On 2023-05-18 17:03:24 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > What about an interval / interval -> double operator that errors out whenever
> > month is non-zero? As far as I can tell that would always be deterministic.
>
> We have months, days, and microseconds, and microseconds-per-day isn't
> much more stable than days-per-month (because DST).

I was about to counter that, if you subtract a timestamp before/after DST
changes, you currently don't get a full day for the "shorter day":

SET timezone = 'America/Los_Angeles';
SELECT '2023-03-13 23:00:00-07'::timestamptz - '2023-03-11 23:00:00-08'::timestamptz;
┌────────────────┐
│    ?column?    │
├────────────────┤
│ 1 day 23:00:00 │
└────────────────┘

which afaics would make it fine to just use 24h days when dividing intervals.


However, that seems to lead to quite broken results:

SET timezone = 'America/Los_Angeles';
WITH s AS (SELECT '2023-03-11 23:00-08'::timestamptz a, '2023-03-13 23:00-07'::timestamptz b) SELECT a, b, b - a AS
b_min_a,a + (b - a) FROM s;
 
┌────────────────────────┬────────────────────────┬────────────────┬────────────────────────┐
│           a            │           b            │    b_min_a     │        ?column?        │
├────────────────────────┼────────────────────────┼────────────────┼────────────────────────┤
│ 2023-03-11 23:00:00-08 │ 2023-03-13 23:00:00-07 │ 1 day 23:00:00 │ 2023-03-13 22:00:00-07 │
└────────────────────────┴────────────────────────┴────────────────┴────────────────────────┘


I subsequently found a comment that seems to reference this in timestamp_mi().
    /*----------
     *    This is wrong, but removing it breaks a lot of regression tests.
     *    For example:
     *


How's this not a significant bug that we need to fix?


I'm not sure this ought to be fixed in timestamp_mi() - perhaps the order of
operations in timestamp_pl_interval() would be a better place?


We probably should document that interval math isn't associative:

postgres[2807421][1]=# SELECT ('2023-03-11 23:00:00-08'::timestamptz + '1 day'::interval) + '23h'::interval;
┌────────────────────────┐
│        ?column?        │
├────────────────────────┤
│ 2023-03-13 22:00:00-07 │
└────────────────────────┘

postgres[2807421][1]=# SELECT ('2023-03-11 23:00:00-08'::timestamptz + '23h'::interval) + '1day'::interval;
┌────────────────────────┐
│        ?column?        │
├────────────────────────┤
│ 2023-03-13 23:00:00-07 │
└────────────────────────┘


Greetings,

Andres Freund