Adding or subtracting a pair of interval values

Download and install the date-time utilities code.

The code on this page depends on the code presented in the section User-defined interval utility functions. This is included in the larger code kit that includes all of the reusable code that the overall date-time section describes and uses.

This section presents a PL/pgSQL implementation of the model that explains how adding two interval values or subtracting one interval value from another works.

Create a table function to display the model in action:

drop function if exists interval_interval_addition_result(interval, interval);

create function interval_interval_addition_result(i1 in interval, i2 in interval)
  returns table(x text)
  language plpgsql
as $body$
declare
  mm_dd_ss_1       constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i1);
  mm_dd_ss_2       constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i2);

  mm_model         constant int                 not null := mm_dd_ss_1.mm + mm_dd_ss_2.mm;
  dd_model         constant int                 not null := mm_dd_ss_1.dd + mm_dd_ss_2.dd;
  ss_model         constant double precision    not null := mm_dd_ss_1.ss + mm_dd_ss_2.ss;

  mm_dd_ss_model   constant interval_mm_dd_ss_t not null := (mm_model, dd_model, ss_model)::interval_mm_dd_ss_t;
  i_model          constant interval            not null := interval_value(mm_dd_ss_model);

  i_actual         constant interval            not null := i1 + i2;
  mm_dd_ss_actual  constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i_actual);
begin
  x := 'input 1 mm_dd_ss:            '||
    to_char(mm_dd_ss_1.mm,      '999999990.9999')||' months' ||
    to_char(mm_dd_ss_1.dd,      '999999990.9999')||' days'   ||
    to_char(mm_dd_ss_1.ss,      '999999990.9999')||' seconds' ; return next;

  x := 'input 2 mm_dd_ss:            '||
    to_char(mm_dd_ss_2.mm,      '999999990.9999')||' months' ||
    to_char(mm_dd_ss_2.dd,      '999999990.9999')||' days'   ||
    to_char(mm_dd_ss_2.ss,      '999999990.9999')||' seconds' ; return next;

  x := 'intermediate model mm_dd_ss: '||
    to_char(mm_model,           '999999990.9999')||' months' ||
    to_char(dd_model,           '999999990.9999')||' days'   ||
    to_char(ss_model,           '999999990.9999')||' seconds' ; return next;

  x := 'ultimate model mm_dd_ss:     '||
    to_char(mm_dd_ss_model.mm,  '999999990.9999')||' months' ||
    to_char(mm_dd_ss_model.dd,  '999999990.9999')||' days'   ||
    to_char(mm_dd_ss_model.ss,  '999999990.9999')||' seconds' ; return next;

  x := 'actual mm_dd_ss:             '||
    to_char(mm_dd_ss_actual.mm, '999999990.9999')||' months' ||
    to_char(mm_dd_ss_actual.dd, '999999990.9999')||' days'   ||
    to_char(mm_dd_ss_actual.ss, '999999990.9999')||' seconds' ; return next;

  x := '';                                                      return next;
  x := 'ultimate model result: '||i_model::text;                return next;
end;
$body$;

Test the function with values that are easy to understand:

select x from interval_interval_addition_result(
  '6 months'::interval,
  '2 days'::interval);

This is the result:

 input 1 mm_dd_ss:                     6.0000 months         0.0000 days         0.0000 seconds
 input 2 mm_dd_ss:                     0.0000 months         2.0000 days         0.0000 seconds
 intermediate model mm_dd_ss:          6.0000 months         2.0000 days         0.0000 seconds
 ultimate model mm_dd_ss:              6.0000 months         2.0000 days         0.0000 seconds
 actual mm_dd_ss:                      6.0000 months         2.0000 days         0.0000 seconds

 ultimate model result: 6 mons 2 days

Test it with values that lead to remainder "spill-down" (in the internal representation) from the mm field to the dd field and from the dd field to the ss field:

select x from interval_interval_addition_result(
  '6.6 months 7.8 days 8 hours'::interval,
  '2.9 months 4.3 days 5 hours'::interval);

This is the result:

 input 1 mm_dd_ss:                     6.0000 months        25.0000 days     97920.0000 seconds
 input 2 mm_dd_ss:                     2.0000 months        31.0000 days     43920.0000 seconds
 intermediate model mm_dd_ss:          8.0000 months        56.0000 days    141840.0000 seconds
 ultimate model mm_dd_ss:              8.0000 months        56.0000 days    141840.0000 seconds
 actual mm_dd_ss:                      8.0000 months        56.0000 days    141840.0000 seconds

 ultimate model result: 8 mons 56 days 39:24:00

Notice that the "spill-down" is an explicit consequence of the design of the algorithm that transforms an interval specification that uses values for each of years, months, days, hours, minutes, and seconds to the target [mm, dd, ss] internal representation. (See the function interval_mm_dd_ss (interval_parameterization_t).) So it affects the calculation of the values mm_dd_ss_1 and mm_dd_ss_2. But thereafter, addition or subtraction of the already integral mm and dd fields can only produce integral totals; and so no further "spill-down" can take place. This is why the "intermediate model mm_dd_ss" row and the "ultimate model mm_dd_ss" row in the output are identical. (Multiplication and division of an interval value by a real number are critically different in this respect.)

This result shows that a practice that the user might adopt to use only interval values that have just a single non-zero mm, dd, or ss value in the internal representation can easily be thwarted by interval-interval addition or subtraction. The section Custom domain types for specializing the native interval functionality shows how you can guarantee that you avoid this problem.

The procedure assert_interval_interval_addition_model_ok() is a mechanical re-write of the function interval_interval_addition_result() that produces no output. Instead, it uses an assert statement to check that the model produces the same result as the native implementation.

This makes it suitable for testing the model with a wide range of input values. Create it thus:

drop procedure if exists assert_interval_interval_addition_model_ok(interval, interval);

create procedure assert_interval_interval_addition_model_ok(i1 in interval, i2 in interval)
  language plpgsql
as $body$
declare
  mm_dd_ss_1      constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i1);
  mm_dd_ss_2      constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i2);

  mm_model        constant int                 not null := mm_dd_ss_1.mm + mm_dd_ss_2.mm;
  dd_model        constant int                 not null := mm_dd_ss_1.dd + mm_dd_ss_2.dd;
  ss_model        constant double precision    not null := mm_dd_ss_1.ss + mm_dd_ss_2.ss;

  mm_dd_ss_model  constant interval_mm_dd_ss_t not null := (mm_model, dd_model, ss_model)::interval_mm_dd_ss_t;
  i_model         constant interval            not null := interval_value(mm_dd_ss_model);

  i_actual        constant interval            not null := i1 + i2;
begin
  assert i_actual == i_model, 'assert failed';
end;
$body$;

Notice the use of the user-defined "strict equals" operator, ==. It's essential to use this, and not the native =, because two interval values that compare as true with the native = operator but as false with the strict == operator can produce different results when added to a timestamptz value—see the section The moment-interval overloads of the "+" and "-" operators. Use the assert procedure thus:

call assert_interval_interval_addition_model_ok(
  '6 months 7 days 8 hours'::interval,
  '2 months 4 days 5 hours'::interval);

call assert_interval_interval_addition_model_ok(
   '6 months 4 days 8 hours'::interval,
  -'2 months 7 days 5 hours'::interval);

call assert_interval_interval_addition_model_ok(
  '6 months 4 days 8 hours'::interval,
  '
      -9.123456 years,
      18.123456 months,
    -700.123456 days,
      97.123456 hours,
    -86.123456 minutes,
      75.123456 seconds
  '::interval);

The tests finish silently, showing that the hypothesised model has not been disproved.