Multiplying or dividing an interval value by a number
This section presents a PL/pgSQL implementation of the model that explains how multiplying or dividing an interval value by a number works. Make sure that you have read the section Adding or subtracting a pair of interval values before reading this section.
Create a table function to display the model in action:
drop function if exists interval_multiplication_result(interval, double precision) cascade;
create function interval_multiplication_result(i in interval, f in double precision)
returns table(z text)
language plpgsql
as $body$
declare
mm_dd_ss_in constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
mm_model constant double precision not null := mm_dd_ss_in.mm*f;
dd_model constant double precision not null := mm_dd_ss_in.dd*f;
ss_model constant double precision not null := mm_dd_ss_in.ss*f;
i_model constant interval not null :=
interval_value(
interval_parameterization(
mm=>mm_model, dd=>dd_model, ss=>ss_model));
mm_dd_ss_model constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i_model);
i_actual constant interval not null := i*f;
mm_dd_ss_actual constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i_actual);
begin
z := 'input mm_dd_ss: '||
to_char(mm_dd_ss_in.mm, '999999990.9999')||' months' ||
to_char(mm_dd_ss_in.dd, '999999990.9999')||' days' ||
to_char(mm_dd_ss_in.ss, '999999990.9999')||' seconds' ; return next;
z := '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;
z := '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;
z := '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;
z := ''; return next;
z := 'ultimate model result: '||i_model::text; return next;
end;
$body$;
Test it first for multiplication and then for division:
select z from interval_multiplication_result('2 months'::interval, 1.2345678);
select z from interval_multiplication_result('2 months'::interval, 0.9876543);
This is the multiplication result:
input mm_dd_ss: 2.0000 months 0.0000 days 0.0000 seconds
intermediate model mm_dd_ss: 2.4691 months 0.0000 days 0.0000 seconds
ultimate model mm_dd_ss: 2.0000 months 14.0000 days 6399.4752 seconds
actual mm_dd_ss: 2.0000 months 14.0000 days 6399.4752 seconds
ultimate model result: 2 mons 14 days 01:46:39.4752
And this is the division result:
input mm_dd_ss: 2.0000 months 0.0000 days 0.0000 seconds
intermediate model mm_dd_ss: 1.9753 months 0.0000 days 0.0000 seconds
ultimate model mm_dd_ss: 1.0000 months 29.0000 days 22399.8912 seconds
actual mm_dd_ss: 1.0000 months 29.0000 days 22399.8912 seconds
ultimate model result: 1 mon 29 days 06:13:19.8912
Notice that because the internal representation for the input has a non-zero value for only the mm field. Then, after both multiplication and division, the intermediate result, of course, still has a non-zero value for only the mm field. However, this non-zero value is now a real number and not an integer. This means that when the ultimate result is constructed, the hard-to-grasp spill-down rules kick in. See the section Modeling the internal representation and comparing the model with the actual implementation. This explains the fact that the ultimate result has non-zero values for the dd and ss fields as well as for the mm field.
This shows that a practice that the user might adopt to use only interval values that have just a single non-zero internal representation field can easily be thwarted by interval multiplication or division. The section Custom domain types for specializing the native interval functionality shows how you can guarantee that you avoid this problem in a way that is consistent with what you can reasonably expect interval multiplication and division to mean.
The procedure assert_interval_multiplication_model_ok() is a mechanical re-write of the function interval_multiplication_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_multiplication_model_ok(interval, double precision);
create procedure assert_interval_multiplication_model_ok(i in interval, f in double precision)
language plpgsql
as $body$
declare
mm_dd_ss_in constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
mm_model constant double precision not null := mm_dd_ss_in.mm*f;
dd_model constant double precision not null := mm_dd_ss_in.dd*f;
ss_model constant double precision not null := mm_dd_ss_in.ss*f;
i_model constant interval not null :=
interval_value(
interval_parameterization(
mm=>mm_model, dd=>dd_model, ss=>ss_model));
i_actual constant interval not null := i*f;
begin
-- Notice the use of the user-defined "strict equals" operator.
assert i_actual == i_model, 'assert failed';
end;
$body$;
Notice the use of the user-defined "strict equals" interval-interval ==
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_multiplication_model_ok('2 months'::interval, 1.2345678);
call assert_interval_multiplication_model_ok('2 months'::interval, 0.9876543);
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 3);
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 0.5);
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 3.5);
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 0.25);
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 1.1);
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 0.9);
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 1.11);
/*
call assert_interval_multiplication_model_ok('2 months 2 days 2 hours'::interval, 0.99);
*/;
call assert_interval_multiplication_model_ok(
'
-9.123456 years,
18.123456 months,
-700.123456 days,
97.123456 hours,
-86.123456 minutes,
75.123456 seconds
'::interval,
1.2345);
/*
call assert_interval_multiplication_model_ok(
'
-9.123456 years,
18.123456 months,
-700.123456 days,
97.123456 hours,
-86.123456 minutes,
75.123456 seconds
'::interval,
0.0009);
*/;
Notice that two of the tests are commented out. The remaining tests finish silently. So the hypothesised model has been disproved. It's easy to see why if the two failing tests are re-cast to use the interval_multiplication_result() function. Try this first:
select z from interval_multiplication_result('2 months 2 days 2 hours'::interval, 0.96);
This is the result:
input mm_dd_ss: 2.0000 months 2.0000 days 7200.0000 seconds
intermediate model mm_dd_ss: 1.9200 months 1.9200 days 6912.0000 seconds
ultimate model mm_dd_ss: 1.0000 months 28.0000 days 138240.0000 seconds
actual mm_dd_ss: 1.0000 months 29.0000 days 51840.0000 seconds
ultimate model result: 1 mon 28 days 38:24:00
Clearly, the result the model differs in the strict sense from the actual result. But compare the results with the native =
operator:
select (
'1 month 28 days 138240 seconds'::interval =
'1 month 29 days 51840 seconds'::interval
)::text;
This result is true. (Of course, with the user-defined strict ==
operator, the result is false.) The explanation is just the same with the other failing test. You look at more failing cases most effectively by using a stripped re-write of the function interval_multiplication_result() that simply returns the interval result without trace output:
drop function if exists interval_multiplication_result(interval, double precision) cascade;
create function interval_multiplication_result(i in interval, f in double precision)
returns interval
language plpgsql
as $body$
declare
mm_dd_ss_in constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
mm_model constant double precision not null := mm_dd_ss_in.mm*f;
dd_model constant double precision not null := mm_dd_ss_in.dd*f;
ss_model constant double precision not null := mm_dd_ss_in.ss*f;
i_model constant interval not null :=
interval_value(
interval_parameterization(
mm=>mm_model, dd=>dd_model, ss=>ss_model));
begin
return i_model;
end;
$body$;
drop operator if exists ** (interval, double precision) cascade;
create operator ** (
leftarg = interval,
rightarg = double precision,
procedure = interval_multiplication_result
);
The user-defined operator allows the test to be terser and therefore easier to read.
Now try two simple tests. First, a test where the model result agrees with the actual result:
select
'2 months 2 days'::interval * 0.95 as "actual",
'2 months 2 days'::interval ** 0.95 as "model";
This is the result:
actual | model
------------------------+------------------------
1 mon 28 days 21:36:00 | 1 mon 28 days 21:36:00
And next, a test where the model result disagrees with the actual result. (The only change from the previous test is that 0.95 is replaced by 0.96.)
select
'2 months 2 days'::interval * 0.96 as "actual",
'2 months 2 days'::interval ** 0.96 as "model";
This is the result:
actual | model
------------------------+------------------------
1 mon 29 days 12:28:48 | 1 mon 28 days 36:28:48
This prompts the obvious question: which of the two alternatives, PostgreSQL's C native code or the PL/pgSQL function interval_multiplication_result() implements the more sensible requirements specification? Here's a good way to choose the winner. Consider this ordinary principle of algebra and arithmetic:
(a + b)*x = a*x +b*x
Test whether the native interval-number *
overload satisfies this rule:
select
('2 months'::interval + '2 days'::interval) * 0.96 as "add then multiply",
('2 months'::interval * 0.96) + ('2 days'::interval * 0.96) as "multiply then add";
This is the result:
add then multiply | multiply then add
------------------------+------------------------
1 mon 29 days 12:28:48 | 1 mon 28 days 36:28:48
So the native interval-number *
overload fails to satisfy the basic rule. Repeat the test using the user-defined **
operator instead of the native *
operator:
select
('2 months'::interval + '2 days'::interval) ** 0.96 as "add then multiply",
('2 months'::interval ** 0.96) + ('2 days'::interval ** 0.96) as "multiply then add";
This is the result:
add then multiply | multiply then add
------------------------+------------------------
1 mon 28 days 36:28:48 | 1 mon 28 days 36:28:48
So the user-defined interval-number **
overload does satisfy the basic rule.
The conclusion is inescapable: the native PostgreSQL implementation of interval-number multiplication is buggy to the extent that, when edge-case input values are presented, the result is not strictly equal to what a sensible specification of the operation's semantics requires. (It's impossible to imagine a specification that would require the observed behavior.) However, when the native, non-strict implementation of the interval-interval overload of the =
operator is used to judge the fidelity of the native interval-number multiplication behavior, the results do accord with what the assumed specification requires.