v2.25 Preview Preview release - not for production v2024.2 LTS Stable release with long-term support v2024.1 STS Stable release with standard-term support v2.20 LTS Stable release with long-term support Unsupported versions

Ad hoc examples of defining interval values

The general way to specify an interval value is by giving values for each of years, months, days, hours, minutes, and seconds as real numbers. The algorithm for computing the internal months, days, and seconds is complex—so much so that a precise statement in prose would be tortuous and hard to comprehend. Rather, the rules are given in the section Modeling the internal representation and comparing the model with the actual implementation as PL/pgSQL code.

The following examples give a flavor of the complexity of the rules.

First example

This specifies only values for the fields that the internal representation uses. And it deliberately uses only integral values for months and days.

select (
    '
            99        months,
           700        days,
      83987851.522816 seconds
    '::interval
  )::text as i;

This is the result:

8 years 3 mons 700 days 23329:57:31.522816

Notice that: 99 months is displayed as 8 years 3 months; 700 days is displayed "as is", even though this duration is longer than two years; and 83987851.522816 seconds is displayed as 23329 hours 57 minutes and 31.522816 seconds even though this duration is longer than two years (and therefore much longer than a month or a day). In other words, neither the days nor the seconds input "spills upwards" in the larger unit direction.

Second example

This specifies only a value for years. But it deliberately specifies this as a real number.

select (
    '
      3.853467 years
    '::interval
  )::text as i;

This is the result:

 3 years 10 mons

Notice that 3.853467 years is 3 years plus 10.241604 months. This explains the 10 mons in the result. But the 0.241604 months remainder did not spill down into days.

Third example

This specifies only a value for months. But it deliberately specifies this as a real number.

select (
    '
      11.674523 months
    '::interval
  )::text as i;

This is the result:

 11 mons 20 days 05:39:23.616

The fractional portion of the months input has spilled down as days—in contrast to the outcome of the second example. Try this continuation of the example:

-- Notice that 0.674523 months = 0.674523*30 days = 20.235690 days.
-- Use only the fractional part now:
select (
    '
      0.235690 days
    '::interval
  )::text as i;

This is the result

 05:39:23.616

This [hours, minutes, seconds] tuple is identical to the trailing part of the initial result.

Fourth example

This specifies only a value for days. But it deliberately specifies this as a real number.

select (
    '
      700.546798 days
    '::interval
  )::text as i;

This is the result:

 700 days 13:07:23.3472

The fractional portion of the days input has spilled down as seconds. This spill-down is reported in hours, minutes, and seconds, as this continuation of the example confirms:

-- Notice that 0.546798 days = 0.546798*24*60*60 seconds = 47243.347200 seconds
select (
    '
      47243.347200 seconds
    '::interval
  )::text as i;

This means that minutes, hours, and seconds are reported. This is the result:

 13:07:23.3472

This [hours, minutes, seconds] tuple is identical to the trailing part of the result above.

The behavior shown in the second example, on the one hand, and the third and fourth examples, on the other hand, is remarkable:

  • If a real number value for years has, after multiplication by 12 to convert it into months, a remainder after extracting the integral months, then this remainder does not spill down to days.
  • But if you specify a real number value for months explicitly, then the remainder after extracting the integral months, does spill down to days.

It's impossible to retrofit a common-sense requirements statement that this asymmetrical behavior meets. You can conclude only that it is simply an emergent behavior of an implementation.

Fifth example

This example highlights another emergent quirk of the implementation.

select
  '-0.54 months 17.4 days'::interval as i1,
  '-0.55 months 17.4 days'::interval as i2;

This is the result:

       i1       |       i2
----------------+-----------------
 1 day 04:48:00 | 1 day -02:24:00

This query helps you visualize what happened:

select
  17.4 - 0.54*30 as "17.4 - 0.54*30",
  17.4 - 0.55*30 as "17.4 - 0.55*30";

This is the result:

 17.4 - 0.54*30 | 17.4 - 0.55*30
----------------+----------------
           1.20 |           0.90

Though 0.9 days is less than 1 day, it has been coerced to an integral value using round() semantics, rather than trunc() semantics, leaving a negative remainder to spill down as hours. Contrast that outcome with this:

select
  '1.2 days'::interval as i1,
  '0.9 days'::interval as i2;

This is the result:

       i1       |    i2
----------------+----------
 1 day 04:48:00 | 21:36:00

In this case, 0.9 days has been coerced to an integral value using trunc() semantics, leaving a positive remainder to spill down as hours. Try this:

select (
    '1 day -02:24:00'::interval
    =
    '21:36:00'::interval)
  ::text as "are they equal?";

This is the result:

 are they equal?
-----------------
 true

You might think that, because the two interval values test as equal, the very strange asymmetry that this fifth example shows has no ultimate consequence. However, the section Interval arithmetic shows you that the two differently-spelled interval values that test as equal actually have different semantics—so arguably the outcome of the equality test shows a bug in the PostgreSQL code that YSQL inherits.

The section Custom domain types for specializing the native interval functionality shows you that, by following the approach that it describes, you will side-step the quirks that these ad hoc examples have revealed without, in fact, sacrificing any useful functionality.

Sixth example

Finally try this. It creates the maximum opportunities for spillage. It also adds complexity by specifying negative values for the years, days, and minutes.

select (
    '
        -9.123456 years,
       18.123456 months,
     -700.123456 days,
       97.123456 hours,
      -86.123456 minutes,
       75.123456 seconds
    '::interval
  )::text as i;

This is the result:

 -7 years -8 mons -697 days +109:38:03.511296

Summary

The outcomes of these six ad hoc tests might seem to be inscrutable. (This doubtless explains the use of "unexpected" in the PostgreSQL documentation.) But they most certainly follow well-defined rules, as the section Modeling the internal representation and comparing the model with the actual implementation shows. You can predict the results of the ad hoc examples shown above—and, indeed, any example that you might care to invent.