v2.25 Preview Preview release - not for production

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

How does YSQL represent an interval value?

The PostgreSQL documentation, under the table Interval Input, says this:

Internally, interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a Daylight Savings Time adjustment is involved. The months and days fields are integers while the seconds field can store fractions. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases, but can cause unexpected results.

Inspection of the C code of the implementation shows that the mm and dd fields of the [mm, dd, ss] internal implementation tuple are four-byte integers. The ss field is an eight-byte integer that records the value in microseconds.

The reference to Daylight Savings Time is a nod to the critical distinction between clock-time-semantics and calendar-time-semantics. Notice the use of "unexpected". It is better to say that your ability confidently to predict the outcome of interval arithmetic rests on a relatively elaborate mental model. This model has two complementary parts:

• How the values of the three fields of the [mm, dd, ss] representation of an interval value are computed when an interval value is created. The present "How does YSQL represent an interval value?" section addresses this.

• The different semantics of these three fields when an interval value is added or subtracted to/from a timestamptz value, a timestamp value, or a time value or when an interval value is created by subtracting one moment (typically a plain timestamp value or a timestamptz_value) from another. This is addressed in the section Interval arithmetic.

As long as you have a robust mental model, then your results will not be unexpected. This section explains the mental model for interval value creation. It enables you to predict what values for months, days, and seconds will be represented internally when you specify an interval value using values for years, months, days, hours, minutes, and seconds. And it enables you to predict what values for years, months, days, hours, minutes, and seconds you will read back from an interval value whose months, days, and seconds values you have managed to predict.

The value recorded by each of the three fields of the representation can be arbitrarily large with respect to the conventions that say, for example, that 25 hours is 1 day and 1 hour. For example, this tuple is allowed: 99 months 700 days 926351.522816 seconds. (Of course, the physical internal representation does impose some limits. See the section interval value limits.)

Note: The internal sixteen-byte format of the internal [mm, dd, ss] representation of an interval value determines the theoretical upper limits on the values of each of the three fields. Other factors determine the actual limits. This is explained in the section interval value limits.

Ad hoc examples

There are no built-in functions or operators that let you display the months, days, and seconds "as is" from the internal representation. Rather, you can display only canonically derived values for years, months, days, hours, minutes, and seconds. The rule for extracting these values from the internal representation is simple and intuitive. It is presented as executable PL/pgSQL in the implementation of the function parameterization (interval_mm_dd_ss_t) in the section User-defined interval utility functions. Briefly, the internal integral months value is displayed as integral years and integral months by taking one year to be 12 months; the internal integral days value is displayed "as is"; and the real number internal seconds is displayed as integral hours, integral minutes, and real number seconds by taking one hour to be 60 minutes and one minute to be 60 seconds.

The section Ad hoc examples of defining interval values provides six examples that give a flavor of the complexity of the rules.

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

The best way to express a statement of the rules that are consistent with the outcomes of the six Ad hoc examples of defining interval values, and any number of other examples that you might try, is to implement an executable simulation and to compare its outputs with the outputs that the actual PostgreSQL, and therefore YSQL, implementations produce.

Note: If you follow the recommendations made below, you can simply skip attempting to understand these tricky rules without sacrificing any useful functionality.

The section Modeling the internal representation and comparing the model with the actual implementation presents this. Here is the algorithm, copied from the body of function interval_mm_dd_ss (interval_parameterization_t):

-- The input values are "p.yy", "p.mm", "p.dd", "p.hh", "p.mi", and "p.ss" — i.e. the
-- conventional parameterization of an "interval" value used by the "::interval" typecast
-- and the "make_interval()" approaches.

-- The output values are "mm_out", "dd_out", and "ss_out" — i.e. the fields of the internal
-- representation tuple.

-- "mm_per_yy", "dd_per_mm", "ss_per_dd", "ss_per_hh", and "ss_per_mi" are constants
-- with the meanings that the mnemonics suggest: the number of months in a year,
-- and so on.

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;

select interval_mm_dd_ss('11.674523 months '::interval)::text;

 (11,20,20363.616)

Possible upcoming implementation change