Comparing two interval values
The semantics of the interval-interval overloads of these comparison operators can be understood in terms of the semantics of the justify_interval() built-in function:
< <= = >= > !=
Modeling the interval-interval comparison test
Implement the model as function modeled_inequality_comparison() thus. It depends upon these two functions, defined in the "User-defined interval utility functions" section:
drop function if exists modeled_inequality_comparison(interval, interval) cascade;
create function modeled_inequality_comparison(i1 in interval, i2 in interval)
returns text
language plpgsql
as $body$
declare
gt constant text not null := 'i1 > i2';
eq constant text not null := 'i1 = i2';
lt constant text not null := 'i1 < i2';
-- This implicity tests that neither "i1" nor "i2" is NULL.
actual_ineq constant text not null := case
when i1 > i2 then gt
when i1 = i2 then eq
when i1 < i2 then lt
end;
s1 constant double precision not null := justified_seconds(i1);
s2 constant double precision not null := justified_seconds(i2);
modeled_ineq constant text not null := case
when s1 > s2 then gt
when s1 = s2 then eq
when s1 < s2 then lt
end;
begin
assert modeled_ineq = actual_ineq, 'Assert #1 failed';
-- Test an alternative model for equality.
if actual_ineq = eq then
declare
r1 constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(justify_interval(i1));
r2 constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(justify_interval(i2));
begin
assert r1 = r2, 'Assert #2 failed';
end;
end if;
return actual_ineq;
end;
$body$;
Notice the use of the assert statement to self-test an alternative definition of the model that works only for the = comparison.
Sanity-check the model by rewriting the code example in the parent section:
select
modeled_inequality_comparison(
'5 days 1 hours'::interval,
'4 days 25 hours'::interval
)::text as e1,
modeled_inequality_comparison(
'5 months 1 day' ::interval,
'4 months 31 days'::interval
)::text as e2;
It finishes silently, showing that the assert holds, and produces this result:
e1 | e2
---------+---------
i1 = i2 | i1 = i2
Here is a more thorough test:
drop procedure if exists test_modeled_inequality_comparison() cascade;
create procedure test_modeled_inequality_comparison()
language plpgsql
as $body$
declare
gt constant text not null := 'i1 > i2';
eq constant text not null := 'i1 = i2';
lt constant text not null := 'i1 < i2';
begin
----------------------------------------------------------
assert
modeled_inequality_comparison(
'1 day 1 second'::interval,
'24 hours '::interval
) = gt,
'Assert #1 failed';
assert
modeled_inequality_comparison(
'1 day '::interval,
'24 hours '::interval
) = eq,
'Assert #2 failed';
assert
modeled_inequality_comparison(
'1 day -1 second'::interval,
'24 hours '::interval
) = lt,
'Assert #3 failed';
----------------------------------------------------------
assert
modeled_inequality_comparison(
'1 day '::interval,
'23 hours 59 minutes 59 seconds'::interval
) = gt,
'Assert #4 failed';
assert
modeled_inequality_comparison(
'1 day '::interval,
'24 hours '::interval
) = eq,
'Assert #5 failed';
assert
modeled_inequality_comparison(
'1 day '::interval,
'24 hours 1 minutes 1 seconds'::interval
) = lt,
'Assert #6 failed';
----------------------------------------------------------
assert
modeled_inequality_comparison(
'10000 years 1 second'::interval,
'3600000 days '::interval
) = gt,
'Assert #7 failed';
assert
modeled_inequality_comparison(
'10000 years '::interval,
'3600000 days '::interval
) = eq,
'Assert #8 failed';
assert
modeled_inequality_comparison(
'10000 years -1 second'::interval,
'3600000 days '::interval
) = lt,
'Assert #9 failed';
----------------------------------------------------------
assert
modeled_inequality_comparison(
'1004 years 2 mons 11 days 04:51:31'::interval,
make_interval(secs=>31234567890)
) = gt,
'Assert #10 failed';
assert
modeled_inequality_comparison(
'1004 years 2 mons 11 days 04:51:31'::interval,
make_interval(secs=>31234567891)
) = eq,
'Assert #11 failed';
assert
modeled_inequality_comparison(
'1004 years 2 mons 11 days 04:51:31'::interval,
make_interval(secs=>31234567892)
) = lt,
'Assert #12 failed';
end;
$body$;
call test_modeled_inequality_comparison();
The procedure finishes without error, showing that each of the assertions holds. In summary, the semantics of all of the interval-interval comparisons can be understood in terms of the semantics of these operators for comparing to real numbers. Each of the to-be-compared interval values is mapped to a real number using the function justified_seconds().
The "strict equals" interval-interval "==" operator
See The user-defined "strict equals" interval-interval == operator in the User-defined interval utility functions section.
(By all means use the native interval-interval = if you're sure that it's the right choice for your use-case.)
Create this function to compare the native = and the strict == behavior:
drop function if exists native_vs_strict_equals(interval, interval) cascade;
create function native_vs_strict_equals(i1 in interval, i2 in interval)
returns text
language plpgsql
as $body$
declare
-- "strict" is a reserved word in PL/pgSQL.
native boolean := i1 = i2;
strict_ boolean := i1 == i2;
native_txt text := coalesce(native ::text, 'null');
strict_txt text := coalesce(strict_ ::text, 'null');
begin
return 'native: ' ||native_txt ||' | strict: ' ||strict_txt;
end;
$body$;
Test it with the following queries. First
select native_vs_strict_equals(
'24 months 120 minutes' ::interval,
' 2 years 2 hours' ::interval
);
This is the result:
native: true | strict: true
The first result shows that, though the spellings of the two literals that define the interval values to be compared are different, they both produce the same mm, dd, ss] internal representations.
Second:
select native_vs_strict_equals(
'1 mon 30 days 126360 seconds'::interval,
'1 mon 31 days 39960 seconds'::interval
);
This is the result:
native: true | strict: false
The second result shows the benefit brought by the "strict" approach.
Finally:
select native_vs_strict_equals(
null::interval,
null::interval
);
This is the result:
native: null | strict: null
This last result simply shows that the user-defined == operator handles null arguments correctly.
The section The moment-interval overloads of the "+" and "-" operators for timestamptz, timestamp, and time demonstrates the benefit brought by the interval-interval == operator.