Thread: Add operator for dividing interval by an interval

Hi,

I've open-coded $subject many times. I wonder if we should add at least a
restricted version of it.

I did find one past discussion of it on the list:
https://www.postgresql.org/message-id/24948.1259797531%40sss.pgh.pa.us

We have workarounds for it on the wiki:

https://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL#4._Multiplication_and_division_of_INTERVALS_is_under_development_and_discussion_at_this_time

There are plenty of search results with various, often quite wrong,
workarounds.


Of course, it's true that there are plenty intervals where division would not
result in clearly determinable result. E.g. '1 month'::interval / '1 day'::interval.

I think there's no clear result whenever the month component is non-zero,
although possibly there are some cases of using months that could be made work
(e.g. '12 months' / '1 month').


In the cases I have wanted interval division, I typically dealt with intervals
without the month component - typically the intervals are the result of
subtracting timestamps or such.

One typical usecase for me is to divide the total runtime of a benchmark by
the time taken for some portion of that (e.g. time spent waiting for IO).


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.

Greetings,

Andres Freund

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).  By the time you
restrict this to give deterministic results, I think it won't be
particularly useful.

You could arbitrarily define months as 30 days and days as 24 hours,
which is what some other interval functions do, and then the double
result would be well-defined; but how useful is it really?

            regards, tom lane

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