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.

The design of the code that this section presents, and the interpretation of the results that it produces, depend on the explanations given in the section How does YSQL represent an interval value?. This is the essential point:

  • An interval value is stored as a [mm, dd, ss] tuple. The mm and dd fields are recorded "as is" as four-byte integers; and the ss field is recorded in microseconds as an eight-byte integer.

Summary

The code examples below illustrate and explain the origin of the limits that this summary sets out. You can regard studying it as optional.

You should appreciate the significance of these limits in the context of the minimum and maximum legal values for the plain timestamp and timestamptz data types: ['4713-01-01 00:00:00 UTC BC', '294276-12-31 23:59:59 UTC AD']. See the note Maximum and minimum supported values on the date-time major section's top page.

Practical limit for the mm field — ±3,587,867

The actual limit is, of course, set by the range that a four-byte integer can represent. But this range is very much bigger than is needed to express the maximum useful interval value using years or months—in other words, the limits for the mm field have no practical consequence. The practical limit is set by the largest mm value, for an interval value with the interval representation [mm, 0, 0], that you can add to the lower limit for timestamp[tz] values, '4713-01-01 00:00:00 BC', without error. This turns out to be 3,587,867 months.

Practical limit for the dd field — ±109,203,489

The actual limit, here too, is set by the range that a four-byte integer can represent. But this range, here too, is very much bigger than is needed to express the maximum useful interval value in days—in other words, the limits for the dd field, too, have no practical consequence. The practical limit is set by the largest dd value, for an interval value with the interval representation [0, dd, 0], that you can add to the lower limit for timestamp[tz] values, '4713-01-01 00:00:00 BC', without error. This turns out to be 109,203,489 days.

Practical limit for the ss field — ±7,730,941,132,799

This limit corresponds to about 244,983 years. This is less than the number of seconds between the minimum and the maximum legal timestamp[tz] values because it's limited by some emergent properties of the implementation. (This is demonstrated in the subsection Limit for the ss field of the internal implementation below.)

This does, therefore, have a nominal practical consequence in that a carelessly implemented subtraction of timestamp values can cause an error. Try this:

select (
  '294276-01-01 00:00:00 AD'::timestamp -
    '4713-01-01 00:00:00 BC'::timestamp);

This is the result:

-104300858 days -08:01:49.551616

The fact that the result is negative is clearly wrong. And a silent wrong results error is the most dangerous kind. The section Interval arithmetic explains how the rules that govern adding or subtracting an interval value to or from a timestamp or timestamptz value are different for the mm, dd, and ss fields. When you understand the rules, you'll see that striving for seconds arithmetic semantics when the duration that the interval value represents is as much, even, as 100 years is arguably meaningless. This means that the actual limitation that the legal ss range imposes has no consequence when you design application code sensibly. However, you must always design your code so that you maximally reduce the chance that a local careless programming error brings a silent wrong results bug. The section Custom domain types for specializing the native interval functionality recommends a regime that enforces proper practice to this end.

Limits for the mm and dd fields of the internal implementation

Create and execute the mm_and_dd_limits() table function to discover the useful practical limits for the mm and dd fields:

-- The domain "ts_t" is a convenient single point of maintenance to allow
-- choosing between "plain timestamp" and "timestamptz" for the test.
drop domain if exists ts_t cascade;
create domain ts_t as timestamptz;

drop function if exists mm_and_dd_limits() cascade;
create function mm_and_dd_limits()
  returns table(z text)
  language plpgsql
as $body$
declare
  t0               constant ts_t     not null := '4713-01-01 00:00:00 UTC BC';
  t                         ts_t     not null := t0;

  mm_limit         constant int      not null := 3587867;
  dd_limit         constant int      not null := 109203489;

  max_mm_interval  constant interval not null := make_interval(months=>mm_limit);
  max_dd_interval  constant interval not null := make_interval(days=>dd_limit);

  one_month        constant interval not null := make_interval(months=>1);
  one_day          constant interval not null := make_interval(days=>1);
begin
  t := t0 + max_mm_interval;
  z := 'max_mm_interval:                    '||rpad(max_mm_interval::text, 22)||
                                               interval_mm_dd_ss(max_mm_interval)::text;     return next;
  z := 't0 + max_mm_interval:               '||t::text;                                      return next;

  begin
    t := t0 + (max_mm_interval + one_month);
  exception when datetime_field_overflow
    -- 22008: timestamp out of range
    then
      z := 't0 + (max_mm_interval + one_month): causes 22008 error';                         return next;
  end;

  z := '';                                                                                   return next;

  t := t0 + max_dd_interval;
  z := 'max_dd_interval:                    '||rpad(max_dd_interval::text, 22)||
                                               interval_mm_dd_ss(max_dd_interval)::text;     return next;
  z := 't0 + max_dd_interval:               '||t::text;                                      return next;

  begin
    t := t0 + (max_dd_interval + one_day);
  exception when datetime_field_overflow
    -- 22008: timestamp out of range
    then
      z := 't0 + (max_dd_interval + one_day):   causes 22008 error';    return next;
  end;
end;
$body$;

set timezone = 'UTC';
select z from mm_and_dd_limits();

This is the result:

 max_mm_interval:                    298988 years 11 mons  (3587867,0,0)
 t0 + max_mm_interval:               294276-12-01 00:00:00+00
 t0 + (max_mm_interval + one_month): causes 22008 error

 max_dd_interval:                    109203489 days        (0,109203489,0)
 t0 + max_dd_interval:               294276-12-31 00:00:00+00
 t0 + (max_dd_interval + one_day):   causes 22008 error

You see the same limits both when you define ts_t as timestamptz and when you define it as plain timestamp.

Limit for the ss field of the internal implementation

The practical limits must be established by empirical testing.

The maximum useful limits are bounded by the duration, in seconds, between the minimum and maximum legal timestamp[tz] values. Try this:

drop function if exists max_seconds() cascade;
create function max_seconds()
  returns table(z text)
  language plpgsql
as $body$
declare
  secs constant double precision not null :=
    extract(epoch from '294276-12-31 23:59:59 UTC AD'::timestamptz) -
    extract(epoch from   '4713-01-01 00:00:00 UTC BC'::timestamptz);
begin
  z := to_char(secs, '9,999,999,999,999');                return next;

  declare
    i   constant interval         not null := make_interval(secs=>secs);
    t            text             not null := '';
    s            double precision not null := 0;
  begin
    begin
      t := i::text;
    exception when datetime_field_overflow then
      z := '22008: "interval out of range" caught.';        return next;
    end;
    begin
      s := extract(seconds from i);
    exception when datetime_field_overflow then
      z := '22008: "interval out of range" caught.';        return next;
    end;
  end;
end;
$body$;

select z from max_seconds();

this is the result:

  9,435,181,535,999
 22008: "interval out of range" caught.
 22008: "interval out of range" caught.

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, i::text or extract(seconds from i) both cause the "interval out of range" error.

The actual limit, in microseconds, is, of course, set by the range that an eight-byte integer can represent. However, empirical tests show that the actual legal range for ss, in seconds, is a lot less than what the representation implies. This is the legal ss range (in seconds):

[-((2^31)*60*60 + 59*60 + 59),  ((2^31 - 1)*60*60 + 59*60 + 59)]  i.e.  [-7730941136399, 7730941132799]

This limiting range was discovered by guesswork and testing. It's presumably brought by the decision made by the PostgreSQL designers that the legal interval values must allow representation as text using the familiar hh:mi:ss notation and that you must be able to use the values for hours, minutes, and seconds that you see in such a representation as inputs to make_interval(). Try this positive test:

select
  interval_mm_dd_ss(make_interval(secs => -7730941136399))::text as "lower limit",
  interval_mm_dd_ss(make_interval(secs =>  7730941132799))::text as "upper limit";

This is the result:

     lower limit      |     upper limit
----------------------+---------------------
 (0,0,-7730941136399) | (0,0,7730941132799)

Go below the lower limit:

select make_interval(secs => -7730941136400)::text;

This causes the "22008: interval out of range" error. Now go above the upper limit:

select make_interval(secs => 7730941132800)::text;

This causes the same "22008" error.

There appears to be a bug in the '::text' typecast of the resulting 'interval' value when the legal lower limit, 'make_interval(secs => -7730941136399') is used.

Try this:

select
  make_interval(secs => -7730941136399)::text as "lower limit",
  make_interval(secs =>  7730941132799)::text as "upper limit";

This is the result:

        lower limit        |       upper limit
---------------------------+-------------------------
 --2147483648:59:58.999552 | 2147483647:59:58.999552

The value for "lower limit", with two leading minus signs, is nonsense. (The value for "upper limit" suffers from a tiny rounding error.) This is why the limits were first demonstrated using the user-defined function interval_mm_dd_ss(), written specially to help the pedagogy in the overall section The interval data type and its variants. Look at its implementation in the section function interval_mm_dd_ss (interval) returns interval_mm_dd_ss_t. You'll see that it uses the extract function to create the interval_mm_dd_ss_t instance that it returns. This apparently doesn't suffer from the bug that the ::text typecast suffers from.

Do this to find the threshold value below which this bug on doing a ::text typecast of an interval value kicks in:

select
  make_interval(secs => -7730941132800)::text as "one below practical lower limit",
  make_interval(secs => -7730941132799)::text as "practical lower limit";

This is the result:

 one below practical lower limit |  practical lower limit
---------------------------------+--------------------------
 --2147483648:00:00              | -2147483647:59:58.999552

Yugabyte therefore recommends that you regard this as the practical range for the ss field of the internal representation:

[-7,730,941,132,799, 7,730,941,132,799]

Confirm that this is sensible like this:

select
  make_interval(secs => -7730941132799)::text as "practical lower limit",
  make_interval(secs =>  7730941132799)::text as "upper limit";

This is the result:

  practical lower limit   |       upper limit
--------------------------+-------------------------
 -2147483647:59:58.999552 | 2147483647:59:58.999552

Finally, try this:

select '7730941132799 seconds'::interval;

It causes the error "22015: interval field value out of range". This is a spurious limitation that the make_interval() approach doesn't suffer from. (In fact, any number that you use in the ::interval typecast approach is limited to the four-byte integer range [-2147483648, 2147483647].) This explains why these three statements cause the "22015: interval field value out of range" error:

select '2147483648 months'  ::interval;
select '2147483648 days'    ::interval;
select '2147483648 seconds' ::interval;

and why these this statement runs without error:

select
  '2147483647 months'  ::interval,
  '2147483647 days'    ::interval,
  '2147483647 seconds' ::interval;

Avoid using the '::interval' typecast approach for constructing an 'interval' value.

Yugabyte recommends that you avoid using the ::interval typecast approach to construct an interval value in application code.

Notice, though, that if you follow Yugabyte's recommendation to use only the months, days, and seconds user-defined domains in application code (see the section Custom domain types for specializing the native interval functionality), and never to use "raw" interval values, then you'll always use the value-constructor functions for these domains and therefore never face the choice between using make_interval() or the ::text typecast of an interval literal.

(By all means, use either of these approaches in ad hoc statements at the ysqlsh prompt.)

It might seem that you lose functionality by following this recommendation because the ::interval typecast approach allows you to specify real number values for years, months, days, hours, and minutes while the make_interval() approach allows only integral values for these parameters. However, the ability to specify non-integral values for parameters other than seconds brings only confusion.

  • The section Modeling the internal representation and comparing the model with the actual implementation shows that the algorithm for computing the [mm, dd, ss] internal representation from the [yy, mm, dd, hh, mi, ss] parameterization, when you typecast the text literal of an interval value, is so complex (and arbitrary) when non-integral values are provided for the first five of the six parameterization values that you are very unlikely to be able usefully to predict what final interval value you'll get.

  • The section Interval arithmetic shows that significantly different semantics governs how each of the fields of the [mm, dd, ss] internal representation is used. Ensure, therefore, that the interval values you create and use have a non-zero value for only one of the three [mm, dd, ss] fields. You can't ensure this if you allow non-integral values for any of the years, months, days, hours, and minutes fields in an interval literal. The section Custom domain types for specializing the native interval functionality shows how to enforce the recommended approach.