PostgreSQL's interval Implementation in C

An interval looks like a duration, but PostgreSQL does not store it as one number. It stores three: a month count, a day count, and a microsecond count, packed into a 16-byte struct. They are never summed into a single value at storage time, because they cannot be — a month is not a fixed number of days, and a day is not a fixed number of hours once daylight saving time is involved. The addition only happens when you compare, and the conversion factor PostgreSQL picks for that comparison is the surprising part.

This is a walk through the C implementation, based on reading the PostgreSQL source (version 20devel, commit e75fcc9867f).

The catalog entry

From src/include/catalog/pg_type.dat (lines 312–318):

text
{ oid => '1186', array_type_oid => '1187',
  descr => 'time interval, format \'number units ...\'',
  typname => 'interval', typlen => '16', typbyval => 'f', typcategory => 'T',
  typispreferred => 't', typinput => 'interval_in', typoutput => 'interval_out',
  typreceive => 'interval_recv', typsend => 'interval_send',
  typmodin => 'intervaltypmodin', typmodout => 'intervaltypmodout',
  typalign => 'd' },

Field	Value	Meaning
`typlen`	`16`	a 16-byte fixed-length value
`typbyval`	`f`	passed by reference (a `Datum` holds a pointer)
`typalign`	`d`	aligned to an 8-byte boundary
`array_type_oid`	`1187`	the `interval[]` array type
`typstorage`	(absent)	defaults to `p`, PLAIN — never TOASTed

interval is typispreferred, so a bare literal like '3 days' resolves to interval rather than to another category-T type.

The C representation

The struct is in src/include/datatype/timestamp.h:

c
/*
 * Storage format for type interval.
 */
typedef struct
{
	TimeOffset	time;			/* all time units other than days, months and
								 * years */
	int32		day;			/* days, after time for alignment */
	int32		month;			/* months and years, after time for alignment */
} Interval;

TimeOffset is int64, so sizeof(Interval) is 8 + 4 + 4 = 16, exactly typlen. Unlike timetz — whose in-memory struct is 16 bytes but whose typlen is 12 — there is no padding discrepancy here: the wide field is placed first so nothing needs to be aligned around it, and the whole struct is what gets copied into the tuple.

text

+------------------+-----------+-----------+
| time  (int64)    | day(int32)| month(int32)
| microseconds     | days      | months
+------------------+-----------+-----------+
 0                  8           12          16

The introductory comment in the same header explains why three fields exist at all:

Interval represents delta time. Keep track of months (and years), days, and hours/minutes/seconds separately since the elapsed time spanned is unknown until instantiated relative to an absolute time.

The broken-down helpers are struct pg_itm (for output and arithmetic) and struct pg_itm_in (for decoding input), both in the same header. pg_itm splits the microseconds back into hours, minutes, seconds, and a remainder, and splits the month count into years and months.

Input and output

interval_in() (src/backend/utils/adt/timestamp.c) parses with the shared ParseDateTime/DecodeInterval pair, falling back to DecodeISO8601Interval when the classic parser rejects the string:

c
	dterr = ParseDateTime(str, workbuf, sizeof(workbuf), field,
						  ftype, MAXDATEFIELDS, &nf);
	if (dterr == 0)
		dterr = DecodeInterval(field, ftype, nf, range,
							   &dtype, itm_in);

	/* if those functions think it's a bad format, try ISO8601 style */
	if (dterr == DTERR_BAD_FORMAT)
		dterr = DecodeISO8601Interval(str,
									  &dtype, itm_in);

The decoder fills a pg_itm_in, and itmin2interval() folds the year/month parts into a single month count and copies the rest across:

c
	int64		total_months = (int64) itm_in->tm_year * MONTHS_PER_YEAR + itm_in->tm_mon;

	if (total_months > INT_MAX || total_months < INT_MIN)
		return -1;
	span->month = (int32) total_months;
	span->day = itm_in->tm_mday;
	span->time = itm_in->tm_usec;

interval_out() goes the other way: interval2itm() explodes the three fields into a pg_itm, and EncodeInterval() renders it according to the IntervalStyle GUC. There are four styles, defined in src/include/miscadmin.h:

c
#define INTSTYLE_POSTGRES			0
#define INTSTYLE_POSTGRES_VERBOSE	1
#define INTSTYLE_SQL_STANDARD		2
#define INTSTYLE_ISO_8601			3

The default is INTSTYLE_POSTGRES, which is why '1 day 02:03:04'::interval prints the way it does, while SET intervalstyle = 'iso_8601' makes the same value print as P1DT2H3M4S.

The binary format is the struct field by field, in memory order (interval_send/interval_recv):

c
	pq_sendint64(&buf, interval->time);
	pq_sendint32(&buf, interval->day);
	pq_sendint32(&buf, interval->month);

Comparison: a 30-day month

This is the interesting part. interval cannot be compared field by field — '1 month' and '30 days' are different structs but the same span. So interval_cmp_internal() collapses both operands to a single number using interval_cmp_value():

c
static inline INT128
interval_cmp_value(const Interval *interval)
{
	INT128		span;
	int64		days;

	/*
	 * Combine the month and day fields into an integral number of days.
	 * Because the inputs are int32, int64 arithmetic suffices here.
	 */
	days = interval->month * INT64CONST(30);
	days += interval->day;

	/* Widen time field to 128 bits */
	span = int64_to_int128(interval->time);

	/* Scale up days to microseconds, forming a 128-bit product */
	int128_add_int64_mul_int64(&span, days, USECS_PER_DAY);

	return span;
}

A month is exactly 30 days, a day is exactly 24 hours, and the result is widened to INT128 because 30 days’ worth of microseconds times an int32 month count overflows int64. This is the same DAYS_PER_MONTH 30 constant used by justify_interval, and it is why:

sql
SELECT interval '1 month' = interval '30 days';   -- true
SELECT interval '1 day'   = interval '24 hours';  -- true
SELECT interval '1 month' = interval '31 days';   -- false

Hashing has to agree with that equivalence, so interval_hash() hashes the same collapsed span — and deliberately only its low 64 bits, “for compatibility with hashes calculated before use of INT128 was introduced”:

c
	INT128		span = interval_cmp_value(interval);
	int64		span64;

	span64 = int128_to_int64(span);

	return DirectFunctionCall1(hashint8, Int64GetDatumFast(span64));

So two intervals that compare equal always hash equal, even though their underlying three fields may differ.

Typmod: two numbers in one int32

interval’s type modifier is unusually dense. The range (which fields are allowed) lives in the high 16 bits and the sub-second precision in the low 16 bits (src/include/utils/timestamp.h):

c
#define INTERVAL_FULL_RANGE (0x7FFF)
#define INTERVAL_FULL_PRECISION (0xFFFF)
#define INTERVAL_TYPMOD(p,r) ((((r) & INTERVAL_RANGE_MASK) << 16) | ((p) & INTERVAL_PRECISION_MASK))
#define INTERVAL_PRECISION(t) ((t) & INTERVAL_PRECISION_MASK)
#define INTERVAL_RANGE(t) (((t) >> 16) & INTERVAL_RANGE_MASK)

The range is a bitmask built from the field codes in src/include/utils/datetime.h (YEAR 2, MONTH 1, DAY 3, HOUR 10, MINUTE 11, SECOND 12), combined by INTERVAL_MASK(b) = (1 << (b)). The header comment there notes the codes “must be in the range 0..14 so that the associated bitmasks can fit into the left half of an INTERVAL’s typmod value”. That is why INTERVAL DAY TO SECOND is representable in a single int32.

AdjustIntervalForTypmod() then truncates or rounds according to that granularity. For INTERVAL YEAR it zeroes the day and time fields; for INTERVAL MINUTE it floors time to a whole minute; the precision half rounds sub-second digits with the same offset trick the timestamp code uses. Note the deliberate rule, spelled out in a comment: fields to the right of the last specified one are zeroed, but fields to the left are left alone, so INTERVAL MONTH and INTERVAL YEAR TO MONTH behave identically.

Normalization is opt-in

Because the three fields are independent, interval '1 day 25 hours' is a legal value: day = 1, time = 25 * USECS_PER_HOUR. The addition operators do not normalize — finite_interval_pl() just adds the fields independently, with overflow checks:

c
	if (pg_add_s32_overflow(span1->month, span2->month, &result->month) ||
		pg_add_s32_overflow(span1->day, span2->day, &result->day) ||
		pg_add_s64_overflow(span1->time, span2->time, &result->time) ||
		INTERVAL_NOT_FINITE(result))
		ereport(ERROR, ...);

Normalization is available only if you ask for it. justify_hours() folds whole days out of the time field into day; justify_days() folds every 30 days out of day into month; justify_interval() does both and also makes all three fields share a sign:

c
	TMODULO(result->time, wholeday, USECS_PER_DAY);
	result->day += wholeday;

	wholemonth = result->day / DAYS_PER_MONTH;
	result->day -= wholemonth * DAYS_PER_MONTH;

There is a beautiful fossil in timestamp_mi() (which computes timestamp - timestamp). The result is an absolute instant difference, so only the time field is set — yet the function then calls interval_justify_hours() with this comment:

c
	/*----------
	 *	This is wrong, but removing it breaks a lot of regression tests.
	 *	For example:
	 *
	 *	test=> SET timezone = 'EST5EDT';
	 *	test=> SELECT
	 *	test-> ('2005-10-30 13:22:00-05'::timestamptz -
	 *	test(>	'2005-10-29 13:22:00-04'::timestamptz);
	 *	 ?column?
	 *	----------------
	 *	 1 day 01:00:00
	 *	 (1 row)
	 *----------
	 */
	result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
											   IntervalPGetDatum(result)));

The 25-hour wall-clock gap across a DST fall-back is reported as “1 day 01:00:00” rather than “25:00:00”, because the code normalizes it. The comment admits the semantics are debatable and that backward compatibility won.

Adding an interval to a timestamp

Here the three fields finally acquire meaning, and they are applied in order: months first, then days, then the microsecond time (timestamp_pl_interval()). Months are added to the calendar, with an end-of-month clamp:

c
		if (pg_add_s32_overflow(tm->tm_mon, span->month, &tm->tm_mon))
			ereport(ERROR, ...);
		if (tm->tm_mon > MONTHS_PER_YEAR)
		{
			tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
			tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
		}
		...
		/* adjust for end of month boundary problems... */
		if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
			tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);

That clamp is why '2024-01-31'::timestamp + interval '1 month' is 2024-02-29, not an error and not March 2. Days are added through the Julian day number (date2j/j2date), and only the microseconds are a plain int64 addition. For timestamptz, timestamptz_pl_interval_internal() recomputes the zone offset with DetermineTimeZoneOffset() after each calendar step, which is what makes “same wall-clock time tomorrow” work across a DST boundary.

interval_mul() cascades fractional results downward — a fractional month becomes days, a fractional day becomes seconds — using DAYS_PER_MONTH and SECS_PER_DAY and TSROUND(), but it never cascades back up. The comment is explicit: “We do NOT cascade up, since we are not forced to do so by the representation. The user can choose to cascade up later, using justify_hours and/or justify_days.”

Infinity

Like the timestamp types, interval reserves its extremes. But because it has three fields, all three must be at their extreme simultaneously:

c
#define INTERVAL_NOBEGIN(i)	\
	do {	\
		(i)->time = PG_INT64_MIN;	\
		(i)->day = PG_INT32_MIN;	\
		(i)->month = PG_INT32_MIN;	\
	} while (0)

#define INTERVAL_IS_NOBEGIN(i)	\
	((i)->month == PG_INT32_MIN && (i)->day == PG_INT32_MIN && (i)->time == PG_INT64_MIN)

Every arithmetic function checks INTERVAL_NOT_FINITE() first, and interval 'infinity' - interval 'infinity' is an error, since the type has nothing equivalent to NaN.

Look at it yourself

The catalog row and the 16-byte size are both directly visible:

sql
SELECT typname, typlen, typbyval, typalign, typarray
FROM pg_type
WHERE typname = 'interval';

SELECT pg_column_size(interval '1 month 2 days 3:04:05');   -- 16

The three-field representation shows through the arithmetic and the comparison rules:

sql
SELECT interval '1 day 25 hours';                      -- 1 day 25:00:00
SELECT justify_hours(interval '1 day 25 hours');       -- 2 days 01:00:00
SELECT justify_days(interval '35 days');               -- 1 mon 5 days
SELECT interval '1 month' = interval '30 days';        -- true

-- months are calendar-aware when added, but 30-day-normalized when compared
SELECT timestamp '2024-01-31' + interval '1 month';    -- 2024-02-29 00:00:00
SELECT extract(epoch FROM interval '1 month');         -- 2592000

That last line is the same 30-day assumption again: extract(epoch ...) in interval_part_common() computes month seconds from DAYS_PER_YEAR/DAYS_PER_MONTH, and DAYS_PER_MONTH is 30.

What it all means

An interval is typlen 16, typbyval f, typalign d, PLAIN storage: a fixed 16-byte struct of time (int64 µs), day (int32), month (int32).
The three fields are never combined at storage time, because the elapsed time depends on the absolute time it is applied to.
Comparison collapses the fields with a 30-day month and a 24-hour day, widened to INT128; hashing uses the same collapsed value (low 64 bits).
Addition and subtraction are field-wise and non-normalizing; justify_hours/justify_days/justify_interval normalize on request.
Adding an interval to a timestamp applies months first (with an end-of-month clamp), then days via Julian conversion, then microseconds.
The typmod packs a field-range bitmask in the high 16 bits and a sub-second precision in the low 16 bits.
Infinity requires all three fields at their extremes simultaneously.