Comparing two interval values

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.

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

(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.