Declaring intervals

Download and [re]install the date-time utilities code.

The code on this page depends on the code presented in the section User-defined interval utility functions and on the function interval_mm_dd_ss (interval_parameterization_t), explained in the section Modeling the internal representation and comparing the model with the actual implementation. This is all included in the larger code kit that includes all of the reusable code that the overall date-time section describes and uses.

Even if you have already installed the code kit, install it again now. Do this because a code example in the section How does YSQL represent an interval value? redefines one of the interval utility functions.

There are over one hundred different spellings of the declaration of an interval. This might seem daunting. However, when you understand the degrees of freedom that the variations exploit, the mental model will seem straightforward. By analogy, when you consider the optional annotations of a bare numeric declaration to specify the scale and precision, you realize that multiplying the numbers of possible spellings for these two annotations gives a vast number of distinct possible spellings. But you need only to understand the concepts of scale and precision. The syntax variants for interval declarations express analogous concepts. This page explains it all. And it also points out that the variations in the syntax spellings can be grouped to reflect the fact that, within each group, the variants all express the same semantics.

However, if you follow the approach described in the section Custom domain types for specializing the native interval functionality, then you will not need to understand what this page explains.

Summary

The explanations and the supporting code below address the semantics of the different spellings of interval declarations—in other words, how these different spellings differently constrain the interval values that can be represented. The code and the explanations presented below show that though there are several syntax spellings for each, there are in fact just six kinds of interval declaration. It's convenient to call these kinds year, month, day, hour, minute, and second. The constraints are applied whenever a new interval value is created, just before recording it in the internal [mm, dd, ss] tuple format. The effects of each of the six constraints are conveniently described by this PL/pgSQL code:

-- Here with [mm, dd, ss] computed with, so far, no constraints.
case mode
  when 'bare' then
    null;
  when 'second' then
    null;
  when 'minute' then
    ss := trunc(ss/60.0)*60.0;
  when 'hour' then
    ss := trunc(ss/(60.0*60.0))*60.0*60.0;
  when 'day' then
    ss := 0.0;
  when 'month' then
    ss := 0.0;
    dd := 0;
  when 'year' then
    ss := 0.0;
    dd := 0;
    mm := (mm/12)*12; -- integer division uses "trunc()" semantics
end case;

Yugabyte recommends using only the bare 'interval' declaration.

The term of art "bare interval" is to be taken literally: no trailing key words, and no (p) precision specifier.

Yugabyte staff members have carefully considered the practical value that these various constraints bring and have concluded that none captures the intent of the SQL Standard or brings useful functionality. The section Custom domain types for specializing the native interval functionality shows how you can usefully constrain interval values, declared using the bare syntax, to allow the internal interval representation to record only:

  • either the years and months fields (corresponding to the mm field in the internal representation).
  • or the days field (corresponding to the dd field in the internal representation).
  • or the hours, minutes, and seconds fields (corresponding to the ss field in the internal representation).

If you follow this recommendation, then you don't need to study the remainder of this section.

The SQL Standard (cosmetically reworded) says this:

There are two classes of intervals. One class, called year-month intervals, has an express or implied date-time precision that includes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an express or implied interval value precision that includes no fields other than DAY, HOUR, MINUTE, and SECOND, though not all are required.

Briefly, day-time intervals implement clock-time-semantics and year-month intervals implement calendar-time-semantics. See the section Two ways of conceiving of time: calendar-time and clock-time.

The PostgreSQL design that YSQL inherits allows, for example, the declaration spelling day to second but, as the tests below show, the day to part of the phrase has no effect and so all six fields (years, months, days, hours, minutes , and seconds) are allowed. This is not useful—in particular because the sharp distinction that the SQL Standard intends between clock-time-semantics and calendar-time-semantics is blurred: hybrid semantics emerge.

However, the regime that the custom domains approach defines is useful because the semantics of interval arithmetic for the values of each of the constrained domains is different—and honors the spirit of the SQL Standard. See the section Interval arithmetic. Moreover, the fact that there are three variants, in contrast to the SQL Standard's two variants, brings beneficial extra functionality.

Interval declaration syntax variants

The PostgreSQL documentation specifies that this is the general syntax for an interval declaration:

interval [ fields ] [ (p) ]

And it states that the fields element restricts the set of stored fields by writing one of these listed phrases (or, as the syntax diagram says, by writing nothing):

            year

            month
year to     month

day

            hour
day to      hour

            minute
day to      minute
hour to     minute

            second
day to      second
hour to     second
minute to   second
<"fields" specification omitted>

This list shows no fewer than fourteen distinct syntax spellings.

The optional (p) element specifies the precision, in microseconds, with which the seconds field of the internal representation records the value. It is legal only after the "second" keyword or after the bare interval declaration. The allowed values are in 0..6. Omitting the (p) element has the same effect as specifying (6). So with the fourteen spellings listed above, together with the eight precision designations (0, 1, 2, 3, 4, 5, 6, or omitting this element) it multiplies up to 112 distinct ways to declare an interval as a table column, in PL/pgSQL code, and so on.

Note: No pair chosen from the fourteen different interval declarations can be used to distinguish procedure or function overloads. The same applies to the variations in the specification of the (p) element.

The account below shows that the different declaration syntaxes produce only six distinct semantic outcomes. The blank lines in the list above, and in the code, reflect this by grouping the syntaxes according to their semantic outcome.

Syntax variants within each of just six groups have the same effect

The anonymous block below demonstrates the syntax groupings. It uses integral values for each of the inputs, except for the seconds, to make the results maximally understandable. The expected outcomes, used on the right hand sides of the putative equalities that the asserts test, are consistent with the explanations that the section How does YSQL represent an interval value? gave of how a parameterization that specifies values using a [yy, mm, dd, hh, mi, ss] tuple is encoded as the internal representation that uses a [mm, dd, ss] tuple with integral mm and dd fields and a real number ss field.

do $body$
declare
  i_bare      constant  interval :=

    make_interval(years=>9, months=>18, days=>700, hours=>97, mins=>86, secs=>75.123456);

  i_year      constant  interval              year    := i_bare;

  i_month_1   constant  interval              month   := i_bare;
  i_month_2   constant  interval  year   to   month   := i_bare;

  i_day       constant  interval              day     := i_bare;

  i_hour_1    constant  interval              hour    := i_bare;
  i_hour_2    constant  interval  day     to  hour    := i_bare;

  i_minute_1  constant  interval              minute  := i_bare;
  i_minute_2  constant  interval  day     to  minute  := i_bare;
  i_minute_3  constant  interval  hour    to  minute  := i_bare;

  i_second_1  constant  interval              second  := i_bare;
  i_second_2  constant  interval  day     to  second  := i_bare;
  i_second_3  constant  interval  hour    to  second  := i_bare;
  i_second_4  constant  interval  minute  to  second  := i_bare;

  r_year    constant text := '10 years';
  r_month   constant text := '10 years 6 mons';
  r_day     constant text := '10 years 6 mons 700 days';
  r_hour    constant text := '10 years 6 mons 700 days 98:00:00';
  r_minute  constant text := '10 years 6 mons 700 days 98:27:00';
  r_second  constant text := '10 years 6 mons 700 days 98:27:15.123456';
begin
  -- "Year" group
  assert i_year::text = r_year, 'i_year = r_year failed';

  -- "Month" group
  assert i_month_1::text = r_month, 'i_month_1 = r_month failed';
  assert i_month_2::text = r_month, 'i_month_2 = r_month failed';

  -- "Day" group
  assert i_day::text = r_day, 'i_day = r_day failed';

  -- "Hour" group
  assert i_hour_1::text = r_hour, 'i_hour_1 = r_hour failed';
  assert i_hour_2::text = r_hour, 'i_hour_2 = r_hour failed';

  -- "Minute" group
  assert i_minute_1::text = r_minute, 'i_minute_1 = r_minute failed';
  assert i_minute_2::text = r_minute, 'i_minute_2 = r_minute failed';
  assert i_minute_3::text = r_minute, 'i_minute_3 = r_minute failed';

  -- "Second" group
  assert i_bare    ::text = r_second, 'i_bare     = r_second failed';
  assert i_second_1::text = r_second, 'i_second_1 = r_second failed';
  assert i_second_2::text = r_second, 'i_second_2 = r_second failed';
  assert i_second_3::text = r_second, 'i_second_3 = r_second failed';
  assert i_second_4::text = r_second, 'i_second_4 = r_second failed';
end;
$body$;

The block finishes silently, showing that each assertion holds.

The declarations are grouped according to the six possible choices for the trailing keyword: year, month, day, hour, minute, or second. The assertions show that the syntax variants within each group have the same effect—in other words that the optional leading phrases, year to, day to, hour to, and minute to have no semantic effect. Only the trailing keyword is semantically significant. Omitting this keyword (i.e. the bare declaration) has the same semantic effect has writing second, and so it belongs in that group.

Each group has different resolution semantics: the choice of trailing keyword determines the least granular unit (years, months, days, hours, minutes, or seconds) that is respected.

The effect of the optional (p) element

Try this:

do $body$
declare
  i_bare constant interval :=

    '9 years 18 months 700 days 97 hours 86 minutes 75.123456 seconds';

  i6 constant interval(6) not null := i_bare;
  i5 constant interval(5) not null := i_bare;
  i4 constant interval(4) not null := i_bare;
  i3 constant interval(3) not null := i_bare;
  i2 constant interval(2) not null := i_bare;
  i1 constant interval(1) not null := i_bare;
  i0 constant interval(0) not null := i_bare;

  r6 constant text := '10 years 6 mons 700 days 98:27:15.123456';
  r5 constant text := '10 years 6 mons 700 days 98:27:15.12346';

-- This shows the "round()" semantics.
  r4 constant text := '10 years 6 mons 700 days 98:27:15.1235';

  r3 constant text := '10 years 6 mons 700 days 98:27:15.123';
  r2 constant text := '10 years 6 mons 700 days 98:27:15.12';
  r1 constant text := '10 years 6 mons 700 days 98:27:15.1';
  r0 constant text := '10 years 6 mons 700 days 98:27:15';
begin
  -- Notice that i_bare, declared as bare "interval",
  -- and i6, declared as "interval(6)" are the same.
  assert i_bare ::text = r6, 'i_bare  = r6 failed';
  assert i6     ::text = r6, 'i6 = r6 failed';

  assert i5     ::text = r5, 'i5 = r5 failed';
  assert i4     ::text = r4, 'i4 = r4 failed';
  assert i3     ::text = r3, 'i3 = r3 failed';
  assert i2     ::text = r2, 'i2 = r2 failed';
  assert i1     ::text = r1, 'i1 = r1 failed';
  assert i0     ::text = r0, 'i0 = r0 failed';
end;
$body$;

The block finishes silently showing that all the assertions hold. This confirms that the (p) element determines the precision, in microseconds, with which the seconds field of the internal representation records the value. Repeat the test after globally replacing interval with interval second in the declarations. The outcome is identical.

Modeling the implementation

Make sure that you've read the section How does YSQL represent an interval value? before reading this section.

The assumption that informs the following test is that any operation that produces an interval value first computes the [mm, dd, ss] internal representation and only then applies the rules that the ad hoc test above illustrates. The rule, expressed in PL/pgSQL code, is shown in the Summary above.

The test provides new overloads for these two functions:

that each adds a mode text formal parameter to express the class name of the interval declaration as one of 'bare', 'year', 'month', 'day', 'hour', 'minute', or 'second', thus:

drop function if exists interval_mm_dd_ss(interval_parameterization_t, text) cascade;

create function interval_mm_dd_ss(p in interval_parameterization_t, mode in text)
  returns interval_mm_dd_ss_t
  language plpgsql
as $body$
declare
  -- Use the single-parameter overload
  mm_dd_ss  constant interval_mm_dd_ss_t not null := interval_mm_dd_ss(p);

  mm                 int                 not null := mm_dd_ss.mm;
  dd                 int                 not null := mm_dd_ss.dd;
  ss                 double precision    not null := mm_dd_ss.ss;
begin
  case mode
    when 'bare' then
      null;
    when 'second' then
      null;
    when 'minute' then
      ss := trunc(ss/60.0)*60.0;
    when 'hour' then
      ss := trunc(ss/(60.0*60.0))*60.0*60.0;
    when 'day' then
      ss := 0.0;
    when 'month' then
      ss := 0.0;
      dd := 0;
    when 'year' then
      ss := 0.0;
      dd := 0;
      mm := (mm/12)*12; -- integer division uses "trunc()" semantics
  end case;
  return (mm, dd, ss)::interval_mm_dd_ss_t;
end;
$body$;

and:

drop function if exists interval_value(interval_parameterization_t, text) cascade;

create function interval_value(p in interval_parameterization_t, mode in text)
  returns interval
  language plpgsql
as $body$
declare
  -- Use the single-parameter overload
  i_bare   constant interval        not null := interval_value(p);

  i_year   constant interval year   not null := i_bare;
  i_month  constant interval month  not null := i_bare;
  i_day    constant interval day    not null := i_bare;
  i_hour   constant interval hour   not null := i_bare;
  i_minute constant interval minute not null := i_bare;
  i_second constant interval second not null := i_bare;
begin
  return
    case mode
      when 'bare'   then i_bare
      when 'year'   then i_year
      when 'month'  then i_month
      when 'day'    then i_day
      when 'hour'   then i_hour
      when 'minute' then i_minute
      when 'second' then i_second
    end;
end;
$body$;

Test the modeled implementation of the constraints in the same way that the unconstrained model was tested in the section Modeling the internal representation and comparing the model with the actual implementation. The procedure assert_model_ok_worker() has the identical implementation to procedure assert_model_ok() in that section.

drop procedure if exists assert_model_ok_worker(interval_parameterization_t, text) cascade;

create procedure assert_model_ok_worker(p in interval_parameterization_t, mode in text)

  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$;

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$
begin
  call assert_model_ok_worker(p, 'bare');
  call assert_model_ok_worker(p, 'year');
  call assert_model_ok_worker(p, 'month');
  call assert_model_ok_worker(p, 'day');
  call assert_model_ok_worker(p, 'hour');
  call assert_model_ok_worker(p, 'minute');
  call assert_model_ok_worker(p, 'second');
end;
$body$;

Execute the tests using the same procedure test_internal_interval_representation_model() that was defined and used to test the unconstrained model. (This is also included in the code kit.)

call test_internal_interval_representation_model();

Each of the tests finishes silently showing that the rules explained above 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. It has an almost identical implementation to the function model_vs_actual_comparison() in the section Modeling the internal representation and comparing the model with the actual implementation. The difference is that the following implentation adds the formal input parameter mode and carries this through to the new overload of interval_mm_dd_ss().

drop function if exists model_vs_actual_comparison(interval_parameterization_t, text) cascade;

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

  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),
  'day');

This is the result:

 modeled:   -9 yy,   -7 mm,   -1 dd,    0 hh,    0 mi,   0.000000 ss
 actual:    -9 yy,   -7 mm,   -1 dd,    0 hh,    0 mi,   0.000000 ss