The moment-moment overloads of the "-" operator for timestamptz, timestamp, and time

The function moment_moment_subtraction() models the algorithm for this operation for a pair of timestamptz values. Create it thus:

drop function if exists moment_moment_subtraction(timestamptz, timestamptz) cascade;

create function moment_moment_subtraction(t1 timestamptz, t2 timestamptz)
  returns interval
  language plpgsql
as $body$
declare
  s1       constant double precision not null := extract(epoch from t1);
  s2       constant double precision not null := extract(epoch from t2);
  i_model  constant interval         not null := justify_hours(make_interval(secs=>(s1 - s2)));

  i_actual constant interval         not null := t1 - t2;
begin
  assert i_model = i_actual, 'assert failed';
  return i_model;
end;
$body$;

The extract(epoch from...) function, for a timestamp argument, determines the number of seconds from the so-called start of the epoch, '1970-01-01 00:00:00', to the specified moment. For a timestamptz argument, the epoch simply starts, as you'd expect, at '1970-01-01 00:00:00 +00'. And for a time argument, the epoch starts at midnight.

See the section justify_hours for the explanation of the semantics of the function. This semantics is dubious for the case of creating an interval value by subtracting one timestamptz value from another because you are very likely to produce a hybrid interval value.

Notice that the moment_moment_subtraction() function uses an assert statement to check that the result of the modeled implementation agrees with that of the actual implementation. Test it like this:

select moment_moment_subtraction(
  '2021-03-14 20:00:00 America/Los_Angeles'::timestamptz,
  '2021-03-13 18:00:00 America/Los_Angeles'::timestamptz);

This is the result:

1 day 01:00:00

Notice that it's hybrid: it has both a days component and a seconds component. Now add this interval value to the earlier of the two timestamptz values between which the difference was calculated and compare it with the later of the two timestamptz values between which the difference was calculated:

select (
    ('2021-03-13 18:00:00 America/Los_Angeles'::timestamptz + '1 day 01:00:00'::interval) =
    ('2021-03-14 20:00:00 America/Los_Angeles'::timestamptz)
  )::text;

The result is false. In other words, this usual rule of arithmetic doesn't apply:

IF c ◄— a - b  THEN  a ◄— c + b

The critical feature of this example with respect to what seems to be a wrong result is that 18:00 on 13-Mar-2021 in the 'America/Los_Angeles' timezone is before the "spring forward" moment when Daylight Savings Time starts, and that 20:00 and 18:00 on 14-Mar-2021 in that timezone is after the "spring forward" moment.

It's possible to understand, and predict, this outcome (and other hybrid interval arithmetic outcomes like it) by understanding the three different kinds of semantics for the moment-interval overloads of the + and - operators for pure mm interval values, pure dd interval values, and pure ss interval values.

These rules are explained in the section The moment-interval overloads of the "+" and "-" operators for timestamptz, timestamp, and time. The rules for the trickiest case, moment-interval arithmetic using a pure days interval value, are explained in exhaustive detail in the section Sensitivity of timestamptz-interval arithmetic to the current timezone.

However, when you have a hybrid dd and mm interval value, you need also to understand the priority rule: in which order are the different variations of addition/subtraction semantics done?

Avoid arithmetic that uses hybrid 'interval' semantics.

Yugabyte staff members have discussed this carefully and believe that the attempt to understand and memorize these rules is counterproductive—and therefore foolish. Rather, you should decide which of the three kinds of semantics your application needs and arrange that you produce only pure, rather than hybrid, interval values. Yugabyte recommends, therefore, that you adopt the practice that the section Custom domain types for specializing the native interval functionality explains.

The section "The moment-interval overloads of the "+" and "-" operators for timestamptz, timestamp, and time", here emphasizes the dangers of hybrid interval arithmetic.