Custom domain types for specializing the native interval functionality

Download the kit to create the custom interval domains.

The code that this page presents is included in a larger set of useful reusable date-time code. In particular, it also installs the User-defined interval utility functions. The custom interval domains code depends on some of these utilities. See the section Download the .zip file to create the reusable code that this overall major section describes.

Each of the sections The moment-moment overloads of the "-" operator and The moment-interval overloads of the "+" and "-" operators makes the point that hybrid interval arithmetic is dangerous and recommends that you should ensure that you create and use only "pure months", "pure seconds", or "pure days" interval values. And they recommend the adoption of the approach that this section describes so that your good practice will be ensured by using its APIs rather than the native interval functionality.

The basic idea is to create a user-defined domain for each of the three kinds of "pure" interval, defining each with a constraint function that ensures the purity and checks that the mm, dd, or ss component of the [mm, dd, ss] internal representation lies within a meaningful range (see the section Interval value limits). Domain-specific functions create a new value of the domain by each of these methods:

  • From a parameterization that uses values, respectively: for years and months; for days; or for hours, minutes, and seconds.
  • By subtracting one timestamptz value from another.
  • By multiplying an existing value of the domain by a real number.

Create the three domains

The design of the code is the same for each of the three domains. The code for the second and third domains is trivially derived, using copy and massage, from the code for the first domain. Each of the functions interval_X_ok() (where X is one of months, days, or seconds) can raise the 23514 pre-defined error (mapped to check_violation in PL/pgSQL) with one of these error texts:

value for domain interval_months_t violates check constraint "interval_months_ok".

or:

value for domain interval_days_t violates check constraint "interval_days_ok".

or:

value for domain interval_seconds_t violates check constraint "interval_seconds_ok".

The hint is function-specific and reflects the detail of how the constraint is violated.

The "interval_months_t" domain

The constraint function interval_months_ok() checks that only the mm component of the [mm, dd, ss] tuple is non-zero and that it lies within a sensible range. It uses the value 3,587,867 for the constant max_mm for the range check. Create the interval_months_t domain thus:

drop domain if exists interval_months_t cascade;
drop function if exists interval_months_ok(interval) cascade;
drop function if exists mm_value_ok(int) cascade;

create function mm_value_ok(mm in int)
  returns text
  language plpgsql
as $body$
declare
  max_mm constant bigint not null := 3587867;
begin
  return
    case abs(mm) > max_mm
      when true then 'Bad mm: '||mm::text||'. Must be in [-'||max_mm||', '||max_mm||'].'
      else           ''
    end;
end;
$body$;

create function interval_months_ok(i in interval)
  returns boolean
  language plpgsql
as $body$
begin
  if i is null then
    return true;
  else
    declare
      mm_dd_ss       constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
      mm             constant int                 not null := mm_dd_ss.mm;
      dd             constant int                 not null := mm_dd_ss.dd;
      ss             constant double precision    not null := mm_dd_ss.ss;
      chk_violation  constant text                not null := '23514';
      msg            constant text                not null :=
                       'value for domain interval_months_t violates check constraint "interval_months_ok".';
    begin
      if dd <> 0 or ss <> 0.0 then
        begin
          raise exception using
            errcode = chk_violation,
            message = msg,
            hint    = case
                        when dd <> 0 and ss <> 0.0 then  'dd = '||dd::text||'. ss = '||ss::text||'. Both must be zero'
                        when dd <> 0               then  'dd = '||dd::text||'. Both dd and ss must be zero'
                        when             ss <> 0.0 then  'ss = '||ss::text||'. Both dd and ss must be zero'
                      end;
        end;
      end if;

      declare
        hint constant text not null := mm_value_ok(mm);
      begin
        if hint <> '' then
          raise exception using
            errcode = chk_violation,
            message = msg,
            hint    = hint;
        end if;
      end;

      return true;
    end;
  end if;
end;
$body$;

create domain interval_months_t as interval
constraint interval_months_ok check(interval_months_ok(value));

Do these four basic sanity tests in order:

select ('1 month 1 day'::interval)::interval_months_t;
select ('1 month 0.000001 sec'::interval)::interval_months_t;
select ('1 month 1 day 0.000001 sec'::interval)::interval_months_t;
select ('3587868 month'::interval)::interval_months_t;

They cause, respectively, these expected errors:

ERROR:  23514: value for domain interval_months_t violates check constraint "interval_months_ok".
HINT:  dd = 1. Both dd and ss must be zero
ERROR:  23514: value for domain interval_months_t violates check constraint "interval_months_ok".
HINT:  ss = 1e-06. Both dd and ss must be zero
ERROR:  23514: value for domain interval_months_t violates check constraint "interval_months_ok".
HINT:  dd = 1. ss = 1e-06. Both must be zero
ERROR:  23514: value for domain interval_months_t violates check constraint "interval_months_ok".
HINT:  Bad mm: 3587868. Must be in [-3587867, 3587867].

The "interval_days_t" domain

The constraint function interval_days_ok() checks that only the dd component of the [mm, dd, ss] tuple is non-zero and that it lies within a sensible range. It uses the value 109,203,489 for the constant max_dd for the range check. Create the interval_days_t domain thus:

drop domain if exists interval_days_t cascade;
drop function if exists interval_days_ok(interval) cascade;
drop function if exists dd_value_ok(int) cascade;

create function dd_value_ok(dd in int)
  returns text
  language plpgsql
as $body$
declare
  max_dd constant bigint not null := 109203489;
begin
  return
    case abs(dd) > max_dd
      when true then 'Bad dd: '||dd::text||'. Must be in [-'||max_dd||', '||max_dd||'].'
      else           ''
    end;
end;
$body$;

create function interval_days_ok(i in interval)
  returns boolean
  language plpgsql
as $body$
begin
  if i is null then
    return true;
  else
    declare
      mm_dd_ss       constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
      mm             constant int                              not null := mm_dd_ss.mm;
      dd             constant int                              not null := mm_dd_ss.dd;
      ss             constant double precision                 not null := mm_dd_ss.ss;
      chk_violation  constant text                             not null := '23514';
      msg            constant text                             not null :=
                       'value for domain interval_days_t violates check constraint "interval_days_ok".';
    begin
      if mm <> 0 or ss <> 0.0 then
        begin
          raise exception using
            errcode = chk_violation,
            message = msg,
            hint    = case
                        when mm <> 0 and ss <> 0.0 then  'mm = '||mm::text||'. ss = '||ss::text||'. Both must be zero'
                        when mm <> 0               then  'mm = '||mm::text||'. Both mm and ss must be zero'
                        when             ss <> 0.0 then  'ss = '||ss::text||'. Both mm and ss must be zero'
                      end;
        end;
      end if;

      declare
        hint constant text not null := dd_value_ok(dd);
      begin
        if hint <> '' then
          raise exception using
            errcode = chk_violation,
            message = msg,
            hint    = hint;
        end if;
      end;

      return true;
    end;
  end if;
end;
$body$;

create domain interval_days_t as interval
constraint interval_days_ok check(interval_days_ok(value));

Do these four basic sanity tests in order:

select ('1 month 1 day'::interval)::interval_days_t;
select ('1 day 0.000001 sec'::interval)::interval_days_t;
select ('1 month 1 day 0.000001 sec'::interval)::interval_days_t;
select ('109203490 day'::interval)::interval_days_t;

They cause, respectively, these expected errors:

ERROR:  23514: value for domain interval_days_t violates check constraint "interval_days_ok".
HINT:  mm = 1. Both mm and ss must be zero
ERROR:  23514: value for domain interval_days_t violates check constraint "interval_days_ok".
HINT:  ss = 1e-06. Both mm and ss must be zero
ERROR:  23514: value for domain interval_days_t violates check constraint "interval_days_ok".
HINT:  mm = 1. ss = 1e-06. Both must be zero
ERROR:  23514: value for domain interval_days_t violates check constraint "interval_days_ok".
HINT:  Bad dd: 109203490. Must be in [-109203489, 109203489].

The "interval_seconds_t" domain

The constraint function interval_seconds_ok() checks that only the ss component of the [mm, dd, ss] tuple is non-zero and that it lies within a sensible range. It uses the value 7,730,941,132,799 for the constant max_ss for the range check. Create the interval_seconds_t domain thus:

drop domain if exists interval_seconds_t cascade;
drop function if exists interval_seconds_ok(interval) cascade;
drop function if exists ss_value_ok(double precision) cascade;

create function ss_value_ok(ss in double precision)
  returns text
  language plpgsql
as $body$
declare
  max_ss constant double precision not null := 7730941132799.0;
begin
  return
    case abs(ss) > max_ss
      when true then 'Bad ss: '||ss::text||'. Must be in [-'||max_ss||', '||max_ss||'].'
      else           ''
    end;
end;
$body$;

create function interval_seconds_ok(i in interval)
  returns boolean
  language plpgsql
as $body$
begin
  if i is null then
    return true;
  else
    declare
      mm_dd_ss       constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i);
      mm             constant int                 not null := mm_dd_ss.mm;
      dd             constant int                 not null := mm_dd_ss.dd;
      ss             constant double precision    not null := mm_dd_ss.ss;
      chk_violation  constant text                not null := '23514';
      msg            constant text                not null :=
                       'value for domain interval_seconds_t violates check constraint "interval_seconds_ok".';
    begin
      if mm <> 0 or dd <> 0 then
        begin
          raise exception using
            errcode = chk_violation,
            message = msg,
            hint    = case
                        when mm <> 0 and dd <> 0 then  'mm = '||mm::text||'. dd = '||dd::text||'. Both must be zero'
                        when mm <> 0             then  'mm = '||mm::text||'. Both mm and dd must be zero'
                        when             dd <> 0 then  'dd = '||dd::text||'. Both mm and dd must be zero'
                      end;
        end;
      end if;

      declare
        hint constant text not null := ss_value_ok(ss);
      begin
        if hint <> '' then
          raise exception using
            errcode = chk_violation,
            message = msg,
            hint    = hint;
        end if;
      end;

      return true;
    end;
  end if;
end;
$body$;

create domain interval_seconds_t as interval
constraint interval_seconds_ok check(interval_seconds_ok(value));

Do these four basic sanity tests in order:

select ('1 month 1 sec'::interval)::interval_seconds_t;
select ('1 day 1 sec'::interval)::interval_seconds_t;
select ('1 month 1 day 1 sec'::interval)::interval_seconds_t;
select make_interval(secs=>7730941132799.01)::interval_seconds_t;

They cause, respectively, these expected errors:

ERROR:  23514: value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
HINT:  mm = 1. Both mm and dd must be zero
ERROR:  23514: value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
HINT:  dd = 1. Both mm and dd must be zero
ERROR:  23514: value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
HINT:  mm = 1. dd = 1. Both must be zero
ERROR:  23514: value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
HINT:  Bad ss: 7730941132799.01. Must be in [-7730941132799, 7730941132799].

Implement the domain-specific functionality

The high-level design of the code is the same for each of the three domains. The detail is domain-specific. The variation is greatest for the function that subtracts one timestamptz value from another.

Only 'timestamptz' overloads are presented here.

If you need to do interval arithmetic with values of the plain timestamp data type, then you'll need to implement overloads for that data type that correspond directly to the overloads for the timestamptz data type that are shown here. You can derive these more-or-less mechanically, with just a little thought.

If you need to do interval arithmetic with values of the time data type, then you'll need only to implement overloads to provide functionality for the interval_seconds_t domain because subtracting one time value from another cannot produce a result as long as one day and because it's meaningless to add days, months, or years to a pure time of day.

Both of these exercises are left to the reader.

Notice that if you follow Yugabyte's advice and persist only timestamptz values, then it's very unlikely that you will need to do interval arithmetic on values of the other moment data types.

Each of the three domains, interval_months_t, interval_days_t, and interval_seconds_t, is provided with a matching set of three functions. Each of these constructs a value of the domain: either using an explicit parameterization; or by subtracting one timestamptz value from another; or by multiplying an existing domain value by a number.

The interval_months_t domain has these functions:

The interval_days_t domain has these functions:

The interval_seconds_t domain has these functions:

The "interval_months_t" domain's functionality

function interval_months (years in int default 0, months in int default 0) returns interval_months_t

This function is parameterized so that you can produce only a "pure months interval value.

drop function if exists interval_months(int, int) cascade;
create function interval_months(years in int default 0, months in int default 0)
  returns interval_months_t
  language plpgsql
as $body$
declare
  mm             constant int  not null := years*12 + months;
  hint           constant text not null := mm_value_ok(mm);
  chk_violation  constant text not null := '23514';
  msg            constant text not null :=
                   'value for domain interval_months_t violates check constraint "interval_months_ok".';
begin
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return make_interval(years=>years, months=>months);
end;
$body$;
function interval_months (t_finish in timestamptz, t_start in timestamptz) returns interval_months_t

This function provides critically useful functionality that is simply missing in the native implementation. There is no way, by directly subtracting one timestamptz value from another, to produce a "pure months" interval value (or even a hybrid interval value whose internal mm component is non-zero). You have to write your own implementation.

When a months interval is added to a starting moment, the finish moment always has the same day-number (in whatever is the finish month) as the start moment has. (If this is not possible, because the target day-number doesn't exist in the target month, then the target day-number is set to that month's biggest day-number.) And it has the same time of day.

When one moment is subtracted from another, the day-number and the time of day of each moment are likely to differ. This implies that the interval result from subtracting one moment from another cannot necessarily produce the finish moment when added back to the start moment.

The specification for this function therefore must depend on asserting a plausible rule. This implementation simply ignores the day number and the time of day of each of the two input moments.

drop function if exists interval_months(timestamptz, timestamptz) cascade;
create function interval_months(t_finish in timestamptz, t_start in timestamptz)
  returns interval_months_t
  language plpgsql
as $body$
declare
  finish_year   constant int     not null := extract(year  from t_finish);
  finish_month  constant int     not null := extract(month from t_finish);
  finish_AD_BC  constant text    not null := to_char(t_finish, 'BC');
  finish_is_BC  constant boolean not null :=
    case
      when finish_AD_BC = 'BC' then true
      when finish_AD_BC = 'AD' then false
    end;

  start_year   constant int not null := extract(year  from t_start);
  start_month  constant int not null := extract(month from t_start);
  start_AD_BC  constant text    not null := to_char(t_start, 'BC');
  start_is_BC  constant boolean not null :=
    case
      when start_AD_BC = 'BC' then true
      when start_AD_BC = 'AD' then false
    end;

  -- There is no "year zero". Therefore, when the two input moments straddle
  -- the AD/BC boundary, we must subtract 12 months to the computed months difference
  diff_as_months constant int not null :=
    (
      (finish_year*12 + finish_month)
      -
      (start_year*12  + start_month)
    )
    - case (finish_is_BC = start_is_BC)
        when true then 0
        else           12
      end;

  hint           constant text not null := mm_value_ok(diff_as_months);
  chk_violation  constant text not null := '23514';
  msg            constant text not null :=
                   'value for domain interval_months_t violates check constraint "interval_months_ok".';
begin
  -- You can reason that "interval_months(largest_legal_timestamptz_value, smallest_legal_timestamptz_value)"
  -- give mm = 3587867 and that because mm_value_ok() tests if this value is exceded, "hint" will always be
  -- the empty string and that the following test is unnecessary. It's done for symmetry and completeness.
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return interval_months(months=>diff_as_months);
end;
$body$;

Test it like this:

select interval_months('2020-06-07 13:47:19 UTC'::timestamptz, '2013-10-23 17:42:09 UTC'::timestamptz);

This is the result:

 6 years 8 mons
function interval_months (i in interval_months_t, f in double precision) returns interval_months_t

The logic of this function is trivial. But it's essential in order to maintain the status of the interval_months_t value as "pure months" under multiplication or division. See the section Multiplying or dividing an interval value by a number. If you multiply a native interval value by a real number (or divide it), then it's more than likely that fractional months will spill down to days and beyond. Try this:

select make_interval(years=>3, months=>99)*0.5378;

This is the result:

 6 years 18 days 02:09:36

Try the native * operator on the corresponding interval_months_t value instead:

select (interval_months(years=>3, months=>99)*0.5378)::interval_months_t;

The attempt causes this error:

ERROR:  23514: value for domain interval_months_t violates check constraint "interval_months_ok".
HINT:  dd = 18. ss = 7776. Both must be zero

The function interval_months(interval_months_t, double precision) fixes this. Create it thus:

drop function if exists interval_months(interval_months_t, double precision) cascade;
create function interval_months(i in interval_months_t, f in double precision)
  returns interval_months_t
  language plpgsql
as $body$
declare
  mm             constant double precision  not null := (interval_mm_dd_ss(i)).mm;
  mm_x_f         constant int               not null := round(mm*f);
  hint           constant text              not null := mm_value_ok(mm_x_f);
  chk_violation  constant text              not null := '23514';
  msg            constant text              not null :=
                   'value for domain interval_months_t violates check constraint "interval_months_ok".';
begin
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return interval_months(months=>mm_x_f);
end;
$body$;

Test it like this:

select interval_months(interval_months(years=>3, months=>99), 0.5378);

This is the result:

6 years 1 mon

Compare this with the result (above) that the native * operator produces with a native interval value:

6 years 18 days 02:09:36

The "impure" part, 18 days 02:09:36, is more than half way through the month, so the approximation is what you'd want. More significantly, you must maintain the purity of the interval value in order to bring understandable semantics under subsequent operations like adding the value to a timestamptz value.

The "interval_days_t" domain's functionality

function interval_days (days in int default 0) returns interval_days_t

This function is parameterized so that you can produce only a "pure days interval value.

drop function if exists interval_days(int) cascade;
create function interval_days(days in int default 0)
  returns interval_days_t
  language plpgsql
as $body$
declare
  hint           constant text not null := dd_value_ok(days);
  chk_violation  constant text not null := '23514';
  msg            constant text not null :=
                   'value for domain interval_days_t violates check constraint "interval_days_ok".';
begin
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return make_interval(days=>days);
end;
$body$;
function interval_days (t_finish in timestamptz, t_start in timestamptz) returns interval_days_t

This function provides critically useful functionality that is simply missing in the native implementation. There is no way, by directly subtracting one timestamptz value from another, to guarantee that you produce a "pure days" interval value. You have to write your own implementation.

When a days interval is added to a starting moment, the finish moment always has the same time of day. But when one moment is subtracted from another, the times of day of each moment are likely to differ.

This implies that the interval result from subtracting one moment from another cannot necessarily produce the finish moment when added back to the start moment.

The specification for this function therefore must depend on asserting a plausible rule. This implementation simply ignores the time of day of each of the two input moments.

drop function if exists interval_days(timestamptz, timestamptz) cascade;
create function interval_days(t_finish in timestamptz, t_start in timestamptz)
  returns interval_days_t
  language plpgsql
as $body$
declare
  d_finish       constant date not null := t_finish::date;
  d_start        constant date not null := t_start::date;
  dd             constant int  not null := d_finish - d_start;
  hint           constant text not null := dd_value_ok(dd);
  chk_violation  constant text not null := '23514';
  msg            constant text not null :=
                   'value for domain interval_days_t violates check constraint "interval_days_ok".';
begin
  -- You can reason that "interval_days(largest_legal_timestamptz_value, smallest_legal_timestamptz_value)"
  -- give dd = 109203489 and that because dd_value_ok() tests if this value is exceded, "hint" will always be
  -- the empty string and that the following test is unnecessary. It's done for symmetry and completeness.
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return interval_days(days=>dd);
end;
$body$;

Test it like this:

select interval_days('2020-06-07 13:47:19 UTC'::timestamptz, '2013-10-23 17:42:09 UTC'::timestamptz);

This is the result:

 2419 days

Notice that the two timestamptz actual arguments for this test are the same as those that were used for the interval_months(timestamptz, timestamptz) test. That produced the result 6 years 8 mons. But (6*12 + 8)*30 is 2400. The disagreement between 2419 days for the interval_days() test and the effective 2400 for the interval_months() test reflects the critical difference between "days interval" arithmetic semantics and "months interval" arithmetic semantics.

function interval_days (i in interval_days_t, f in double precision) returns interval_days_t

The logic of this function is trivial. But it's essential in order to maintain the status of the interval_days_t value as "pure days" under multiplication or division. See the section Multiplying or dividing an interval value by a number. If you multiply a native interval value by a real number (or divide it), then it's more than likely that fractional days will spill down to hours and beyond. Try this:

select make_interval(days=>99)*7.5378;

This is the result:

 746 days 05:48:46.08

Try the native * operator on the corresponding interval_days_t value instead:

select (interval_days(days=>99)*7.5378)::interval_days_t;

The attempt causes this error:

ERROR:  23514: value for domain interval_days_t violates check constraint "interval_days_ok".
HINT:  ss = 20926.08. Both mm and ss must be zero

The function interval_days(interval_days_t, double precision) fixes this. Create it thus:

drop function if exists interval_days(interval_days_t, double precision) cascade;
create function interval_days(i in interval_days_t, f in double precision)
  returns interval_days_t
  language plpgsql
as $body$
declare
  dd             constant double precision not null := (interval_mm_dd_ss(i)).dd;
  dd_x_f         constant int              not null := round(dd*f);
  hint           constant text             not null := dd_value_ok(dd_x_f);
  chk_violation  constant text             not null := '23514';
  msg            constant text             not null :=
                   'value for domain interval_days_t violates check constraint "interval_days_ok".';
begin
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return interval_days(days=>dd_x_f);
end;
$body$;

Test it like this:

select interval_days(interval_days(days=>99), 7.5378);

This is the result:

 746 days

Compare this with the result (above) that the native * operator produces with a native interval value:

 746 days 05:48:46.08

You must accept the approximation in order to maintain the purity of the interval value. This is the only way to bring understandable semantics under subsequent operations like adding the value to a timestamptz value.

The "interval_seconds_t" domain's functionality

function interval_seconds (hours in int default 0, mins in int default 0, secs in double precision default 0.0) returns interval_seconds_t

it's important to work around the delayed manifestation of the 22008 error

Try this:

do $body$
declare
  i constant interval not null := make_interval(secs=>7730941132800);
begin
  raise info 'Hello';
end;
$body$;

Notice 7730941132800 is greater than the limit of 7730941132799 that the subsection Limit for the ss field of the internal implementation gives. Yet it runs without error and reports "Hello". The error manifests only when you try to use the interval value i. Try this:

do $body$
declare
  i constant interval not null := make_interval(secs=>7730941132800);
  t          text     not null := '';
begin
  t := i::text;
  raise info 'i: %', t;
end;
$body$;

This causes the error "22008: interval out of range". It's reported for the assignment t := i::text. This is a different example of the effect that the "Limit for the ss field of the internal implementation" subsection describes thus:

Anomalously, the evaluation of "i := make_interval(secs=>secs)" (where secs is 9,435,181,535,999) silently succeeds. But the attempts to use it with, for example, extract(seconds from i) or i::text both cause the "interval out of range" error.

Without working around this effect, The invocation interval_seconds(secs=>7730941132800) would report this confusing error:

ERROR:  22008: interval out of range
CONTEXT: ...while casting return value to function's return type

The function interval_seconds(int, int, double precision) is parameterized so that you can produce only a "pure seconds" interval value. Create it thus:

drop function if exists interval_seconds(int, int, double precision) cascade;
create function interval_seconds(hours in int default 0, mins in int default 0, secs in double precision default 0.0)
  returns interval_seconds_t
  language plpgsql
as $body$
declare
  ss             constant double
                          precision not null := (hours::double precision)*60*60 + (mins::double precision)*60 + secs;
  hint           constant text not null := ss_value_ok(ss);
  chk_violation  constant text not null := '23514';
  msg            constant text not null :=
                   'value for domain interval_seconds_t violates check constraint "interval_seconds_ok".';
begin
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return make_interval(hours=>hours, mins=>mins, secs=>secs);
end;
$body$;

Do this basic sanity test:

select interval_seconds(secs=>7730941132800);

This is the result:

ERROR:  23514: value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
HINT:  Bad ss: 7730941132800. Must be in [-7730941132799, 7730941132799].
function interval_seconds (t_finish in timestamptz, t_start in timestamptz) returns interval_seconds_t

This function provides critically useful functionality that is simply missing in the native implementation. There is no way to guarantee that you produce a "pure seconds" interval value unless you write your own implementation. (For anything bigger than 24 hours, using the native functionality, you get a hybrid "days-seconds" interval value.)

drop function if exists interval_seconds(timestamptz, timestamptz) cascade;
create function interval_seconds(t_finish in timestamptz, t_start in timestamptz)
  returns interval_seconds_t
  language plpgsql
as $body$
declare
  s_finish       constant double precision not null := extract(epoch from t_finish);
  s_start        constant double precision not null := extract(epoch from t_start);
  ss             constant double precision not null := s_finish - s_start;
  hint           constant text not null := ss_value_ok(ss);
  chk_violation  constant text not null := '23514';
  msg            constant text not null :=
                   'value for domain interval_seconds_t violates check constraint "interval_seconds_ok".';
begin
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return interval_seconds(secs=>ss);
end;
$body$;

Try this positive test:

with c as (
  select
    interval_seconds(
      '247003-10-10 19:59:59 UTC'::timestamptz,
        '2020-01-01:12:00:00 UTC'::timestamptz)
    as i)
select
  interval_mm_dd_ss(i),
  i::text
from c;

This is the result:

  interval_mm_dd_ss  |            i
---------------------+-------------------------
 (0,0,7730941132799) | 2147483647:59:58.999552

Notice that the result suffers from a tiny rounding error. It seems to be inconceivable that an application would need clock time semantics when the to-be-differenced timestamptz values are about two hundred and fifty millennia apart—and so this rounding error won't matter.

Try this negative test:

select
  interval_seconds(
    '247003-10-10 20:00:00 UTC'::timestamptz,
      '2020-01-01:12:00:00 UTC'::timestamptz);

As expected, it causes this error:

ERROR:  23514: value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
HINT:  Bad ss: 7730941132800. Must be in [-7730941132799, 7730941132799].
function interval_seconds (i in interval_seconds_t, f in double precision) returns interval_seconds_t

The logic of this function is trivial. Moreover, it isn't essential because the ss field of the internal [mm, dd, ss] tuple is a real number with microseconds precision and there is no "spill up" possibility from the ss field to the dd, or mm, fields. Try this:

select make_interval(hours=>99)*3.6297;

This is the result:

 359:20:25.08

The native * operator on the corresponding interval_seconds_t value also runs without error:

select (interval_seconds(hours=>99)*3.6297)::interval_seconds_t;

It brings the same result as when you use the native interval.

The function interval_seconds(interval_seconds_t, double precision) is provided just in the interests of symmetry. Create it thus:

drop function if exists interval_seconds(interval_seconds_t, double precision) cascade;
create function interval_seconds(i in interval_seconds_t, f in double precision)
  returns interval_seconds_t
  language plpgsql
as $body$
declare
  ss             constant double precision not null := (interval_mm_dd_ss(i)).ss;
  ss_x_f         constant double precision not null := ss*f;
  hint           constant text             not null := ss_value_ok(ss_x_f);
  chk_violation  constant text             not null := '23514';
  msg            constant text             not null :=
                   'value for domain interval_seconds_t violates check constraint "interval_seconds_ok".';
begin
  if hint <> '' then
    raise exception using
      errcode = chk_violation,
      message = msg,
      hint    = hint;
  end if;
  return interval_seconds(secs=>ss_x_f);
end;
$body$;

Test it like this:

select interval_seconds(interval_seconds(hours=>99), 3.6297);

Once again, It brings the same result as when you use the native interval. Now push it beyond the limit with a huge multiplier:

select interval_seconds(interval_seconds(hours=>99), 100000000);

It causes this error:

ERROR:  23514: value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
HINT:  Bad ss: 35640000000000. Must be in [-7730941132799, 7730941132799]

Basic demonstration using one month expressed as a months interval, a days, interval, and a seconds interval.

This test uses the rules of thumb that one month is always thirty days and one day is always twenty-four hours. Create the table function test_results() thus:

drop function if exists test_results() cascade;
create function test_results()
  returns table(z text)
  language plpgsql
as $body$
declare
  t0                    constant timestamptz         not null := '2021-03-13 20:00 America/Los_Angeles';

  i_months              constant interval_months_t   not null := interval_months (months => 1);
  i_days                constant interval_days_t     not null := interval_days   (days   => 30);
  i_seconds             constant interval_seconds_t  not null := interval_seconds(hours  => 30*24);

  t0_plus_i_months      constant timestamptz         not null := t0 + i_months;
  t0_plus_i_days        constant timestamptz         not null := t0 + i_days;
  t0_plus_i_seconds     constant timestamptz         not null := t0 + i_seconds;

  calculated_i_months   constant interval_months_t   not null := interval_months (t0_plus_i_months,  t0);
  calculated_i_days     constant interval_days_t     not null := interval_days   (t0_plus_i_days,    t0);
  calculated_i_seconds  constant interval_seconds_t  not null := interval_seconds(t0_plus_i_seconds, t0);
begin
  assert calculated_i_months  = i_months,  'calculated_i_months  <> i_months';
  assert calculated_i_days    = i_days,    'calculated_i_days    <> i_days';
  assert calculated_i_seconds = i_seconds, 'calculated_i_seconds <> i_seconds';

  z := 't0'||rpad(' ', 40)||t0::text;                                         return next;

  z := 'i_months:  '||rpad(interval_mm_dd_ss(i_months)::text,  15)||
       't0 + i_months:  '||t0_plus_i_months::text;                            return next;

  z := 'i_days:    '||rpad(interval_mm_dd_ss(i_days)::text,    15)||
       't0 + i_days:    '||t0_plus_i_days::text;                              return next;

  z := 'i_seconds: '||rpad(interval_mm_dd_ss(i_seconds)::text, 15)||
       't0 + i_seconds: '||t0_plus_i_seconds::text;                           return next;
end;
$body$;

Execute it using a timezone where the interval values cross the spring-forward moment:

set timezone = 'America/Los_Angeles';
select z from test_results();

This is the result:

 t0                                        2021-03-13 20:00:00-08
 i_months:  (1,0,0)        t0 + i_months:  2021-04-13 20:00:00-07
 i_days:    (0,30,0)       t0 + i_days:    2021-04-12 20:00:00-07
 i_seconds: (0,0,2592000)  t0 + i_seconds: 2021-04-12 21:00:00-07

Each test result is different from the other two and is consistent, respectively, with the semantic definitions of months calendar time durations, days calendar time durations, and seconds clock time durations:

  • The test that uses the interval_months_t domain advances the month by one while keeping the day number the same, even though it starts from a date in March which has thirty-one days. And it keeps the local time the same even though the timezone offset has sprung forward from minus eight hours to minus seven hours.

  • The test that uses the interval_days_t domain advances the day by thirty days. Because it starts from the thirteenth of March, which has thirty-one days, it finishes on the twelfth of April. It keeps the local time the same even though the timezone offset has sprung forward from minus eight hours to minus seven hours.

  • The test that uses the interval_seconds_t domain advances the day by thirty days to finish on the twelfth of April. It started at 20:00 local time. But because it has crossed the spring forward moment, it finishes at 21:00 local time.

Thoroughly test the whole apparatus

Create and execute the following procedure to assert that all the expected outcomes hold:

drop procedure if exists do_tests() cascade;

create procedure do_tests()
  language plpgsql
as $body$
declare
  -- Define all timestamptz values using a zero tz offset.
  -- Fair interpretation of "max legal value is 294276 AD"
  -- and "min legal value is 4713 BC".
  ts_max  constant timestamptz not null := '294276-01-01 00:00:00 UTC AD';
  ts_min  constant timestamptz not null :=   '4713-01-01 00:00:00 UTC BC';

  ts_1    constant timestamptz not null :=   '2021-01-01 00:00:00 UTC AD';
  ts_2    constant timestamptz not null :=   '2000-01-01 00:00:13 UTC AD';
  ts_3    constant timestamptz not null := '294275-06-01 00:00:00 UTC AD';
  ts_4    constant timestamptz not null := '294275-06-01 00:00:13 UTC AD';

  ts_5    constant timestamptz not null := '240271-10-10 07:59:59 UTC AD';
begin
  -- Do all tests using session tz 'UTC'
  set timezone = 'UTC';

  <<"interval_months_t tests">>
  begin
    <<"Test #1">>
    -- Check that given "i = ts_max - ts_min", then "ts_min + i = ts_max".
    declare
      i      constant interval_months_t not null := interval_months(ts_max, ts_min);
      ts_new constant timestamptz       not null := ts_min + i;
    begin
      assert (ts_new = ts_max), 'Test #1 failure';
    end "Test #1";

    <<"Test #2">>
    -- Check that when ts_2 and ts_1 differ in their dd, hh, mi, or ss values,
    -- given "i = ts_1 - ts_2", then "ts_2 + i <> ts_1".
    declare
      i       constant interval_months_t not null := interval_months(ts_1, ts_2);
      ts_new  constant timestamptz       not null := ts_2 + i;
    begin
      assert (ts_new <> ts_1), 'Test #2 failure';
    end "Test #2";
  end "interval_months_t tests";

  <<"interval_days_t tests">>
  begin
    <<"Test #3">>
    -- Check that given "i = ts_max - ts_min", then "ts_min + i = ts_max"
    -- for the full "ts_max, ts_min" range,
    declare
      i      constant interval_days_t not null := interval_days(ts_max, ts_min);
      ts_new constant timestamptz     not null := ts_min + i;
    begin
      assert (ts_new = ts_max), 'Test #3 failure';
    end "Test #3";

    <<"Test #4">>
    -- Check that given "i = ts_3 - ts_min", then "ts_min + i = ts_3"
    -- where ts_3 and ts_min differ by their day number but have their hh:mi:ss the same.
    declare
      i       constant interval_days_t not null := interval_days(ts_3, ts_min);
      ts_new  constant timestamptz     not null := ts_min + i;
    begin
      assert (ts_new = ts_3), 'Test #4 failure';
    end "Test #4";

    <<"Test #5">>
    -- Check that when ts_2 and ts_1 differ in their hh, mi, or ss values,
    -- given "i = ts_4 - ts_min", then "ts_min + i <> ts_4".
    declare
      i       constant interval_days_t not null := interval_days(ts_4, ts_min);
      ts_new  constant timestamptz     not null := ts_min + i;
    begin
      assert (ts_new <> ts_4), 'Test #5 failure';
    end "Test #5";
  end "interval_days_t tests";

  <<"interval_seconds_t tests">>
  begin
    <<"Test #6">>
    -- Check that given "i = ts_5 - ts_min", then "ts_min + i = ts_5"
    -- for the full "ts_5, ts_min" range,
    declare
      i       constant interval_seconds_t not null := interval_seconds(ts_5, ts_min);
      ts_new  constant timestamptz        not null := ts_min + i;
      ts_tol  constant double precision   not null := 0.0005;
    begin
      -- date_trunc('milliseconds', t) is too blunt an instrument.
      assert
        (abs(extract(epoch from ts_new) - extract(epoch from ts_5)) < ts_tol),
        'Test #6 failure';
    end "Test #6";
  end "interval_seconds_t tests";

  <<"Test #7">>
  -- Outcomes from interval multiplication/division.
  declare
    months_result   constant interval_months_t  not null := interval_months (years=>6, months=>1);
    days_result     constant interval_days_t    not null := interval_days   (days=>746);
    seconds_result  constant interval_seconds_t not null := interval_seconds(hours=>359, mins=>20, secs=>25.08);
  begin
    assert (
      -- Notice the use of the "strict equals" operator.
      interval_months(interval_months(years=>3, months=>99), 0.5378) == months_result  and
      interval_days(interval_days(days=>99), 7.5378)                 == days_result    and
      interval_seconds(interval_seconds(hours=>99), 3.6297)          == seconds_result
      ), 'Test #7 failure';
  end "Test #7";

  <<"Test #8">>
  -- Months to days ratio.
  declare
    m      constant interval_months_t not null := interval_months(ts_max, ts_min);
    mm     constant double precision  not null := (interval_mm_dd_ss(m)).mm;
    ym     constant double precision  not null := mm/12.0;

    d      constant interval_days_t   not null := interval_days  (ts_max, ts_min);
    dd     constant double precision  not null := (interval_mm_dd_ss(d)).dd;

    yd     constant double precision  not null := dd/365.2425;

    ratio  constant double precision  not null := abs(ym -yd)/greatest(ym, yd);
  begin
    assert ratio < 0.000001, 'Test #8 failure';
  end "Test #8";

end;
$body$;

call do_tests();

It finishes silently, showing that all the assertions hold.

Comparing the results of interval_seconds(), interval_days(), and interval_months() for the same timestamptz pair

The table function seconds_days_months_comparison() creates a report thus:

  • It uses the secs actual argument value to create the interval_seconds_t value i_secs.

  • It initializes the timestamptz value t0 to the earliest moment that PostgreSQL, and therefore YSQL, support.

  • It initializes the timestamptz value t1 to the sum of t0 and i_secs.

  • It initializes i_days using interval_days(t1, t0).

  • It initializes i_months using interval_months(t1, t0).

  • It evaluates interval_mm_dd_ss() for each of these interval domain values and reports the ss value that i_secs represents, the dd value that i_days represents, and the mm value that i_months represents.

  • It converts each of the values ss, dd, and mm to a real number of years using these facts: the fixed number of seconds per day is 24*60*60 and the fixed number of months per year is 12; and the average number of days per year is 365.2425 (see the Wikipedia article Year).

  • It reports the values that it has calculated.

365.2425 or 365.25 for the average number of days per year?

The Wikipedia article Year gives both 365.2425 days and 365.25 days as the average number of days per year. The first figure (used in the code below) is the average according to the Gregorian scheme. And the second figure is the average according to the Julian scheme. The extract(epoch from interval_value) built-in function section presents a PL/pgSQL model for this function. This uses 365.25 days as the average number of days per year in order to produce the same result as does the native implementation that it models. (The designers of PostgreSQL might well have chosen to use 365.2425 days—but they happened not to. The choice is arbitrary.) However, the nominal durations of the three kinds of interval in the test below are closer to each other when 365.2425 days is used.

Create the table function thus:

drop function if exists seconds_days_months_comparison(double precision) cascade;

create function seconds_days_months_comparison(secs in double precision)
  returns table(x text)
  language plpgsql
as $body$
declare
  err                            text             not null := '';
  msg                            text             not null := '';
  hint                           text             not null := '';
  seconds_per_day       constant double precision not null := 24*60*60;
  avg_days_per_year     constant double precision not null := 365.2425;
  avg_seconds_per_year  constant double precision not null := seconds_per_day*avg_days_per_year;
  months_per_year       constant double precision not null := 12;
begin
  x := 'secs input:  '||secs;                                                   return next;

  set timezone = 'UTC';
  declare
    i_secs  constant interval_seconds_t not null := interval_seconds(secs=>secs);
    t0      constant timestamptz        not null := '4713-01-01 00:00:00 UTC BC';
    t1      constant timestamptz        not null := t0 + i_secs;
  begin
    declare
      i_days       constant interval_days_t   not null := interval_days  (t1, t0);
      i_months     constant interval_months_t not null := interval_months(t1, t0);

      ss           constant double precision  not null := (interval_mm_dd_ss(i_secs  )).ss;
      dd           constant int               not null := (interval_mm_dd_ss(i_days  )).dd;
      mm           constant int               not null := (interval_mm_dd_ss(i_months)).mm;

      yrs_from_ss  constant numeric           not null := round((ss/avg_seconds_per_year)::numeric, 3);
      yrs_from_dd  constant numeric           not null := round((dd/avg_days_per_year   )::numeric, 3);
      yrs_from_mm  constant numeric           not null := round((mm/months_per_year     )::numeric, 3);
    begin
      x := 't0:          '||lpad(to_char(t0, 'yyyy-mm-dd hh24:mi:ss BC'), 25);  return next;
      x := 't1:          '||lpad(to_char(t1, 'yyyy-mm-dd hh24:mi:ss BC'), 25);  return next;
      x := '';                                                                  return next;
      x := 'i_secs:      '||i_secs::text;                                       return next;
      x := 'i_days:      '||i_days::text;                                       return next;
      x := 'i_months:    '||i_months::text;                                     return next;
      x := '';                                                                  return next;
      x := 'yrs_from_ss  '||yrs_from_ss;                                        return next;
      x := 'yrs_from_dd: '||yrs_from_dd;                                        return next;
      x := 'yrs_from_mm: '||yrs_from_mm;                                        return next;
    end;
  end;
exception when check_violation then
  get stacked diagnostics
    err = returned_sqlstate,
    msg = message_text,
    hint = pg_exception_hint;

  x := '';                                                                      return next;
  x := 'ERROR: '||err;                                                          return next;
  x := msg;                                                                     return next;
  x := hint;                                                                    return next;
end;
$body$;

Invoke the function like this, using the biggest legal interval_seconds_t value:

set timezone = 'UTC';
select x from seconds_days_months_comparison(7730941132799);

This is the result:

 secs input:  7730941132799
 t0:             4713-01-01 00:00:00 BC
 t1:           240271-10-10 07:59:58 AD

 i_secs:      2147483647:59:58.999552
 i_days:      89478485 days
 i_months:    244983 years 9 mons

 yrs_from_ss  244983.772
 yrs_from_dd: 244983.771
 yrs_from_mm: 244983.750

Notice that the real numbers of years calculated from each of the "pure seconds", "pure days", and "pure months" interval values are in very close agreement. The fact that they are so close, yet do differ from each other, reflects these facts:

  • The interval_months() implementation uses calendar-time-semantics and disregards the time of day and the day number in the month.
  • The interval_days() implementation uses calendar-time-semantics and disregards the time of day.
  • The interval_seconds() implementation uses clock-time-semantics and is exact to a microsecond precision.
  • The duration of about 250 millennia is so big that the rounding errors brought by calendar-time-semantics show up only as tens of milliseconds.

Now invoke the function using much smaller durations. First, like this:

set timezone = 'UTC';
select x from seconds_days_months_comparison(200000000000);

This is the result:

 secs input:  200000000000
 t0:             4713-01-01 00:00:00 BC
 t1:             1625-09-30 19:33:20 AD

 i_secs:      55555555:33:20
 i_days:      2314814 days
 i_months:    6337 years 8 mons

 yrs_from_ss  6337.748
 yrs_from_dd: 6337.745
 yrs_from_mm: 6337.667

And secondly like this:

set timezone = 'UTC';
select x from seconds_days_months_comparison(8854000);

This is the result:

 secs input:  8854000
 t0:             4713-01-01 00:00:00 BC
 t1:             4713-04-12 11:26:40 BC

 i_secs:      2459:26:40
 i_days:      102 days
 i_months:    3 mons

 yrs_from_ss  0.281
 yrs_from_dd: 0.279
 yrs_from_mm: 0.250

The input number of seconds was chosen by trial and error so that: the time of day component of t1 is about half way through the day; and the day number in the month is about half way through the month. Here, of course, the real numbers of years calculated from each of the "pure seconds", "pure days", and "pure months" interval values are in rather poor agreement.

Finally, test the error behavior with an input number of seconds that exceeds the maximum value that the interval_seconds() constructor function allows by one second:

select x from seconds_days_months_comparison(7730941132800);

This is the result, as expected:

 secs input:  7730941132800

 ERROR: 23514
 value for domain interval_seconds_t violates check constraint "interval_seconds_ok".
 Bad ss: 7730941132800. Must be in [-7730941132799, 7730941132799].