PostgreSQL's date Implementation in C

A PostgreSQL date is not a Unix timestamp, not a struct tm, and not “days since 1970”. It is a signed 32-bit integer counting days since 2000-01-01. That single decision is why a date sorts with a plain integer comparison, why date - date is an int4, and why 'infinity'::date is literally INT32_MAX.

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

The catalog entry

Every type starts as a row in src/include/catalog/pg_type.dat. For date (lines 287–290):

{ oid => '1082', array_type_oid => '1182', descr => 'date',
  typname => 'date', typlen => '4', typbyval => 't', typcategory => 'D',
  typinput => 'date_in', typoutput => 'date_out', typreceive => 'date_recv',
  typsend => 'date_send', typalign => 'i' },

Four facts fall out of this:

There is no typstorage, so it defaults to p (PLAIN) — no compression, no TOAST. A date is always exactly 4 bytes on disk, and there is nothing to decompress.

The C representation

DateADT is a one-line typedef in src/include/utils/date.h:

typedef int32 DateADT;

Because the type is by-value, the accessors are trivial. Datum is the generic 8-byte value container PostgreSQL uses everywhere (src/include/postgres.h), and the conversion is just a cast:

static inline DateADT
DatumGetDateADT(Datum X)
{
	return (DateADT) DatumGetInt32(X);
}

static inline Datum
DateADTGetDatum(DateADT X)
{
	return Int32GetDatum(X);
}

The infinity sentinels are reserved at the extremes of the type (src/include/utils/date.h):

#define DATEVAL_NOBEGIN		((DateADT) PG_INT32_MIN)
#define DATEVAL_NOEND		((DateADT) PG_INT32_MAX)

-2147483648 is -infinity; 2147483647 is infinity. Everything in between is a day count.

Days since 2000, via Julian day

The internal value is julian_day - POSTGRES_EPOCH_JDATE, where POSTGRES_EPOCH_JDATE is 2451545, the Julian day of 2000-01-01 (src/include/datatype/timestamp.h):

#define UNIX_EPOCH_JDATE		2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE	2451545 /* == date2j(2000, 1, 1) */

The Julian-day machinery lives in src/backend/utils/adt/datetime.c, not date.c. date2j() turns a calendar date into a Julian day number:

int
date2j(int year, int month, int day)
{
	int			julian;
	int			century;

	if (month > 2)
	{
		month += 1;
		year += 4800;
	}
	else
	{
		month += 13;
		year += 4799;
	}

	century = year / 100;
	julian = year * 365 - 32167;
	julian += year / 4 - century + century / 4;
	julian += 7834 * month / 256 + day;

	return julian;
}

This is integer-only, so it cannot produce a rounding error, and it works for negative years (BC). The inverse, j2date(), is in the same file. The whole range check is a pair of integer comparisons:

#define IS_VALID_DATE(d) \
	((DATETIME_MIN_JULIAN - POSTGRES_EPOCH_JDATE) <= (d) && \
	 (d) < (DATE_END_JULIAN - POSTGRES_EPOCH_JDATE))

That allows Julian day zero (4714-11-24 BC) up to 5874897-12-31 AD.

Input and output

date_in() (src/backend/utils/adt/date.c) hands the string to the shared parser, then converts:

	dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
						  field, ftype, MAXDATEFIELDS, &nf);
	if (dterr == 0)
		dterr = DecodeDateTime(field, ftype, nf,
							   &dtype, tm, &fsec, &tzp, &extra);
	...
	date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;

	/* Now check for just-out-of-range dates */
	if (!IS_VALID_DATE(date))
		ereturn(escontext, (Datum) 0, ...);

DecodeDateTime returns a dtype, and date_in accepts three special cases besides an ordinary date: DTK_EPOCH ('epoch', i.e. 1970-01-01), DTK_LATE ('infinity'), and DTK_EARLY ('-infinity'). Note that a date never carries a timezone, so tzp is ignored here.

date_out() is the mirror image:

	j2date(date + POSTGRES_EPOCH_JDATE,
		   &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
	EncodeDateOnly(tm, DateStyle, buf);

The binary format is the bare 4 bytes. date_recv() reads them with pq_getmsgint(buf, sizeof(DateADT)) and range-checks; date_send() writes them with pq_sendint32().

Comparison, sorting, hashing

Because the internal value is an integer day count, ordering is free. date_cmp() is a three-way integer compare, and the btree sort support hooks it straight to the int32 comparator:

Datum
date_sortsupport(PG_FUNCTION_ARGS)
{
	SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);

	ssup->comparator = ssup_datum_int32_cmp;
	PG_RETURN_VOID();
}

Hashing is just hash_uint32(PG_GETARG_DATEADT(0)) in hashdate(). There is no normalization step, no locale, no calendar logic in the hot path — the expensive work is all done once, at input.

Arithmetic

Adding days is integer addition with an overflow guard (date_pli()):

	result = dateVal + days;

	/* Check for integer overflow and out-of-allowed-range */
	if ((days >= 0 ? (result < dateVal) : (result > dateVal)) ||
		!IS_VALID_DATE(result))
		ereport(ERROR, ...);

date_mi() returns the difference as an int4, and refuses to subtract infinite dates. The infinity macros make the special cases cheap: adding a day to infinity returns infinity, because date_pli() short-circuits on DATE_NOT_FINITE().

Look at it yourself

The catalog row is directly inspectable:

SELECT typname, typlen, typbyval, typalign, typarray
FROM pg_type
WHERE typname IN ('date', 'time', 'timetz');

To see the actual bytes, use pageinspect and tuple_data_split (contrib/pageinspect/heapfuncs.c), which returns one bytea per attribute:

CREATE EXTENSION pageinspect;
CREATE TABLE d(t date);
INSERT INTO d VALUES ('2024-01-01');

SELECT s.t_data
FROM heap_page_items(get_raw_page('d', 0)) h,
     LATERAL unnest(tuple_data_split('d'::regclass, h.t_data,
                                     h.t_infomask, h.t_infomask2,
                                     h.t_bits)) AS s(t_data);

That returns the 4 raw bytes of the date. You can also confirm the epoch by subtraction:

SELECT '2024-01-01'::date - '2000-01-01'::date;   -- 8766
SELECT 'infinity'::date, '-infinity'::date;       -- infinity | -infinity

What it all means

• A date is int32 days since 2000-01-01, not a Unix timestamp and not a struct tm.
• The Julian-day conversion is pure integer arithmetic, so it is exact and works for BC dates.
• typlen = 4, typbyval = t, typalign = i, PLAIN storage: always 4 bytes, never TOASTed.
• Sorting and hashing are plain int32 operations — the calendar logic is only paid at parse time.
• infinity/-infinity are reserved at INT32_MAX/INT32_MIN, and the arithmetic short-circuits around them.

Further reading

• src/include/catalog/pg_type.dat — the date catalog entry.
• src/include/utils/date.h — DateADT, infinity macros, accessors.
• src/include/datatype/timestamp.h — POSTGRES_EPOCH_JDATE, IS_VALID_DATE.
• src/backend/utils/adt/date.c — date_in, date_out, date_recv, date_send, arithmetic.
• src/backend/utils/adt/datetime.c — date2j, j2date, EncodeDateOnly.