Modeling the internal representation and comparing the model with the actual implementation

Download and install the date-time utilities code.

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.

Each of the ad hoc examples creates an interval value by specifying any subset of its parameterization using years, months, days, hours, minutes, and seconds from at least one of these through at most all six of them. (Omitting any of these six has the same effect as specifying zero for that parameter.)

This section explains the algorithm that derives the fields of the [mm, dd, ss] representation tuple from the six years, months, days, hours, minutes, and seconds input values by modeling it using PL/pgSQL.

Function to create an instance of the modeled internal representation of an interval value

The function interval_mm_dd_ss(interval_parameterization_t) accepts values for the subset of interest of the years, months, days, hours, minutes, and seconds parameterization that each of the ::interval typecast construction method and the make_interval() built-in function construction method uses. It returns an interval_mm_dd_ss_t instance.

The implementation of the function therefore acts as the promised documentation of the algorithm.

function interval_mm_dd_ss (interval_parameterization_t) returns interval_mm_dd_ss_t

drop function if exists interval_mm_dd_ss(interval_parameterization_t) cascade;

create function interval_mm_dd_ss(p in interval_parameterization_t)
  returns interval_mm_dd_ss_t
  language plpgsql
as $body$
declare
  mm_per_yy               constant double precision not null := 12.0;
  dd_per_mm               constant double precision not null := 30.0;
  ss_per_dd               constant double precision not null := 24.0*60.0*60.0;
  ss_per_hh               constant double precision not null := 60.0*60.0;
  ss_per_mi               constant double precision not null := 60.0;

  mm_trunc                constant int              not null := trunc(p.mm);
  mm_remainder            constant double precision not null := p.mm - mm_trunc::double precision;

  -- This is a quirk.
  mm_out                  constant int              not null := trunc(p.yy*mm_per_yy) + mm_trunc;

  dd_real_from_mm         constant double precision not null := mm_remainder*dd_per_mm;

  dd_int_from_mm          constant int              not null := trunc(dd_real_from_mm);
  dd_remainder_from_mm    constant double precision not null := dd_real_from_mm - dd_int_from_mm::double precision;

  dd_int_from_user        constant int              not null := trunc(p.dd);
  dd_remainder_from_user  constant double precision not null := p.dd - dd_int_from_user::double precision;

  dd_out                  constant int              not null := dd_int_from_mm + dd_int_from_user;

  d_remainder             constant double precision not null := dd_remainder_from_mm + dd_remainder_from_user;

  ss_out                  constant double precision not null := d_remainder*ss_per_dd +
                                                                p.hh*ss_per_hh +
                                                                p.mi*ss_per_mi +
                                                                p.ss;
begin
  return (mm_out, dd_out, ss_out)::interval_mm_dd_ss_t;
end;
$body$;

Notice how the quirks that the ad hoc examples highlight are modeled.

  • A fractional years part, after subtracting the integral part, carries "right" as INTEGRAL months. But, when there's a remainder after dividing this by 12, this does not carry further to days.
  • A fractional months part, after subtracting the integral part, does carry "right" as REAL days.
  • A fractional days part, after subtracting the integral part, carries "right" as REAL hours.
  • The INTEGRAL days value is calculated by first truncating (a) the carry-over from months and (b) the truncation of the user-supplied value and then adding these. And the carry-over to hours is computed by adding the individually calculated remainders from steps (a) and (b).

All this seems to be counter-intuitive. But the need to do the calculation this way is pinpointed by the second and fifth ad hoc tests.

The algorithm that this function implements was designed by humanly analyzing very many ad hoc observations like those shown above. The hypothesis that the algorithm embodies was tested as described below.

Test that the hypothesized rules are consistent with the observations that reflect the rules of the actual implementation

Create a procedure to encapsulate the assertion that the rules that govern the simulation are the same as the rules that govern the actual implementation and a procedure to call this basic assert encapsulation with a range of parameterizxations.

procedure assert_model_ok(interval_parameterization_t)

drop procedure if exists assert_model_ok(interval_parameterization_t) cascade;

create procedure assert_model_ok(p in interval_parameterization_t)
  language plpgsql
as $body$
declare
  i_modeled        constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(p);
  i_from_modeled   constant interval            not null := interval_value(i_modeled);
  i_actual         constant interval            not null := interval_value(p);
  mm_dd_ss_actual  constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(i_actual);

  p_modeled  constant interval_parameterization_t not null := parameterization(i_modeled);
  p_actual   constant interval_parameterization_t not null := parameterization(i_actual);
begin
  -- Belt-and-braces check for mutual consistency among the "interval" utilities.
  assert (i_modeled      ~= mm_dd_ss_actual), 'assert #1 failed';
  assert (p_modeled      ~= p_actual       ), 'assert #2 failed';
  assert (i_from_modeled == i_actual       ), 'assert #3 failed';
end;
$body$;

Now test it on a set of input values that includes those used for the ad hoc tests shown above.

procedure test_internal_interval_representation_model()

drop procedure if exists test_internal_interval_representation_model() cascade;

create procedure test_internal_interval_representation_model()
  language plpgsql
as $body$
begin
  call assert_model_ok(interval_parameterization());

  call assert_model_ok(interval_parameterization(
    mm =>       99,
    dd =>      700,
    ss => 83987851.522816));

  call assert_model_ok(interval_parameterization(
    yy => 3.853467));

  call assert_model_ok(interval_parameterization(
    mm => 11.674523));

  call assert_model_ok(interval_parameterization(
    dd => 0.235690));

  call assert_model_ok(interval_parameterization(
    dd => 700.546798));

  call assert_model_ok(interval_parameterization(
    ss => 47243.347200));

  call assert_model_ok(interval_parameterization(
    mm => -0.54,
    dd => 17.4));

  call assert_model_ok(interval_parameterization(
    mm => -0.55,
    dd => 17.4));

  call assert_model_ok(interval_parameterization(
    mm =>  0.11,
    dd => -1));

  call assert_model_ok(interval_parameterization(
    mm =>  0.12,
    dd => -1));

  call assert_model_ok(interval_parameterization(
    dd => 1.2));

  call assert_model_ok(interval_parameterization(
    dd => 0.9));

  call assert_model_ok(interval_parameterization(
    dd => 1,
    hh => -2,
    mi => 24,
    ss => 0));

  call assert_model_ok(interval_parameterization(
    dd => 0,
    hh => 21,
    mi => 36,
    ss => 0));

  call assert_model_ok(interval_parameterization(
    yy =>  19,
    mm => -1,
    dd =>  17,
    hh => -100,
    mi =>  87,
    ss => -76));

  call assert_model_ok(interval_parameterization(
    yy =>  9.7,
    mm => -1.55,
    dd =>  17.4,
    hh => -99.7,
    mi =>  86.7,
    ss => -75.7));

  call assert_model_ok(interval_parameterization(
    yy => -9.7,
    mm =>  1.55,
    dd => -17.4,
    hh =>  99.7,
    mi => -86.7,
    ss =>  75.7));
  end;
$body$;

Invoke it like this:

call test_internal_interval_representation_model();

Each call of assert_model_ok() finishes silently showing that the hypothesized rules have so far been shown always to agree with the rules of the actual implementation. You are challenged to disprove the hypothesis by inventing more tests. If any of your tests causes assert_model_ok() to finish with an assert failure, then raise a GitHub issue against this documentation section.

If you do find such a counter-example, you can compare the results from using the actual implementation and the modeled implementation with this re-write of the logic of the assert_model_ok() procedure. Instead of using assert, it shows you the outputs for visual comparison:

function model_vs_actual_comparison (interval_parameterization_t) returns table(x text)

drop function if exists model_vs_actual_comparison(interval_parameterization_t) cascade;

create function model_vs_actual_comparison(p in interval_parameterization_t)
  returns table(x text)
  language plpgsql
as $body$
declare
  i_modeled        constant interval_mm_dd_ss_t         not null := interval_mm_dd_ss(p);
  i_actual         constant interval                    not null := interval_value(p);

  p_modeled        constant interval_parameterization_t not null := parameterization(i_modeled);
  p_actual         constant interval_parameterization_t not null := parameterization(i_actual);

  ss_modeled_text  constant text                        not null := ltrim(to_char(p_modeled.ss, '9999999999990.999999'));
  ss_actual_text   constant text                        not null := ltrim(to_char(p_actual.ss,  '9999999999990.999999'));
begin
  x := 'modeled: '||
    lpad(p_modeled.yy ::text,  4)||' yy, '||
    lpad(p_modeled.mm ::text,  4)||' mm, '||
    lpad(p_modeled.dd ::text,  4)||' dd, '||
    lpad(p_modeled.hh ::text,  4)||' hh, '||
    lpad(p_modeled.mi ::text,  4)||' mi, '||
    lpad(ss_modeled_text,     10)||' ss';                           return next;

  x := 'actual:  '||
    lpad(p_actual.yy  ::text,  4)||' yy, '||
    lpad(p_actual.mm  ::text,  4)||' mm, '||
    lpad(p_actual.dd  ::text,  4)||' dd, '||
    lpad(p_actual.hh  ::text,  4)||' hh, '||
    lpad(p_actual.mi  ::text,  4)||' mi, '||
    lpad(ss_actual_text,      10)||' ss';                           return next;
end;
$body$;

Use it like this:

select x from model_vs_actual_comparison(interval_parameterization(
  yy => -9.7,
  mm =>  1.55,
  dd => -17.4,
  hh =>  99.7,
  mi => -86.7,
  ss =>  75.7));

This is the result:

 modeled:   -9 yy,   -7 mm,   -1 dd,  100 hh,   40 mi,  33.700000 ss
 actual:    -9 yy,   -7 mm,   -1 dd,  100 hh,   40 mi,  33.700000 ss

Alternative approach using make_interval()

The make_interval() built-in procedure has int input formal parameters for years, months, days, hours, and minutes; and it has a double precision input formal parameter for seconds. It also, as a bonus, has an int input formal parameter for weeks (where this is treated simply as 7 days). This parameterization suggests that the PostgreSQL designers saw no use-case for supplying the quantities that use int input formal parameters as real numbers and that it's an unintended consequence of the ::interval typecast approach to construct an interval value that it allows a parameterization that provides real numbers for these values. Nevertheless, the implementation supports this and therefore must be documented, as the page has done.

You can substitute the following alternative implementation of the interval_value() function for the implementation given above:

function interval_value(interval_parameterization_t) returns interval — replacement implementation

drop function if exists interval_value(interval_parameterization_t) cascade;

create function interval_value(p in interval_parameterization_t)
  returns interval
  language plpgsql
as $body$
declare
  yy int not null := p.yy::int;
  mm int not null := p.mm::int;
  dd int not null := p.dd::int;
  hh int not null := p.hh::int;
  mi int not null := p.mi::int;
begin
  return make_interval(
    years  => yy,
    months => mm,
    days   => dd,
    hours  => hh,
    mins   => mi,
    secs   => p.ss);
end;
$body$;

Of course, to test it you must supply only integral values for years, months, days, hours, and minutes like this:

call assert_model_ok(interval_parameterization());

call assert_model_ok(interval_parameterization(
  mm =>       99,
  dd =>      700,
  ss => 83987851.522816
));

call assert_model_ok(interval_parameterization(
  yy => 3,
  mm => 8));

call assert_model_ok(interval_parameterization(
  mm => 11,
  dd => 17));

call assert_model_ok(interval_parameterization(
  hh => 123456));

call assert_model_ok(interval_parameterization(
  dd => 123456));

call assert_model_ok(interval_parameterization(
  ss => 47243.347200));

call assert_model_ok(interval_parameterization(
  mm => -1,
  dd => 17));

call assert_model_ok(interval_parameterization(
  mm =>  11,
  dd => -1));

call assert_model_ok(interval_parameterization(
  dd => 1,
  hh => -2,
  mi => 24,
  ss => 0));

call assert_model_ok(interval_parameterization(
  dd => 0,
  hh => 21,
  mi => 36,
  ss => 0));

call assert_model_ok(interval_parameterization(
  yy =>  19,
  mm => -1,
  dd =>  17,
  hh => -100,
  mi =>  87,
  ss => -76));

Each of the calls to assert_model_ok() finishes silently, showing that the assertions that it tests hold. Many more, and more varied, tests than are shown here were run while this example was being developed. The tested assertions have never been seen to fail.