Adding or subtracting a pair of interval values
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.