# ADR 0001 — Store partial dates as ISO 8601 strings in one column

**Status:** accepted

## Decision

A partial date is stored as its ISO 8601 form, in one text column and nothing else:

| Precision | Stored as | Example |
|---|---|---|
| Year | `YYYY` | `1990` |
| Month | `YYYY-MM` | `1990-06` |
| Day | `YYYY-MM-DD` | `1990-06-14` |

- The column is a `varchar(10)`. Its collation compares text byte by byte, which is PostgreSQL's
  `C` collation. The field sets this itself.
- The field adds no companion columns, and asks the model to declare none.
- A part that was not recorded is not stored. Nothing fills it with a first day or a first month.
- The years are four digits, 0001 to 9999.
- These three forms are EDTF level 0 (ISO 8601-2). Nothing else is accepted.

## Why

### A partial date is uncertain, and the column must say so

A value recorded as "1990" means a single day somewhere in 1990. Nobody knows which day. Any
storage that writes a day or a month into the column claims a certainty the data does not have.
Someone reading the database, or another tool reading it, takes 1990-01-01 for a recorded
1 January.

Four ways of storing a partial date were weighed against that.

| Option | Why it was rejected |
|---|---|
| A timestamp with the precision held in its seconds | Stores a day and a month nobody recorded (`1990-01-01 00:00:00`). The precision can be read only through the field's own code. The column is time-zone aware, so Django's `__year`, `__month` and `__date` lookups convert to the current time zone first. On a site west of UTC, a value stored as 1990 then matches `__year=1989`. |
| A date column and a precision column | Honest about the precision, but Django has no field that spans two columns. It needs a hidden companion field added to every model, and two fields in every migration. |
| A start date and an end date, or a PostgreSQL `daterange` | Turns an uncertain date into an interval whose ends look certain. A reader of `1990-01-01 – 1990-12-31` cannot tell it from a recorded interval. It needs a hidden companion column, or works only on PostgreSQL. |
| **The ISO 8601 string** | Chosen. |

### What the string gives

- **It is the standard form.** DataCite, schema.org and CSL JSON all accept these three forms as
  they are, so exporting a value needs no conversion.
- **Text order is date order.** Every part is fixed-width and zero-padded, so comparing two
  values as text orders them chronologically. `order_by`, `gte` and `lte` in SQL give the same
  order as the value type in Python. A year sorts before the months in it, and a month before the
  days in it: `1990`, `1990-06`, `1990-06-14`, `1991`.
- **Range filters work on one indexed column.** "Collected between June 1990 and 23 September
  2020" is `value >= '1990-06' AND value <= '2020-09-23'`, an index range scan.
- **No time zone is involved.** The column holds no time, so no lookup converts it.
- **It reads correctly everywhere.** In the database, in `dumpdata`, in django-reversion's
  history and in any tool that reads the table.

### What a filter on a partial date means

A filter compares strings, and partial dates are uncertain, so some matches are uncertain too.
This package documents what each filter matches rather than hiding the question behind a stored
range. With the range above:

- `1990-06-14` and `2020-09` are inside it.
- `1990` is outside it, because `'1990' < '1990-06'`. The sample might have been collected in
  July 1990, or in March.
- `2020` is inside it, because `'2020' <= '2020-09-23'`. The sample might have been collected in
  December 2020.

A developer who needs a different rule writes it with the lookups the package provides.

### Queries beyond ranges

| Need | How |
|---|---|
| Records from June of any year | A `__month` lookup reading characters 6–7. A year-only value has no month, so it never matches. |
| A date axis on a chart | A `__start` lookup, giving the first day of the period as a real `date` in the database. In Python the value type gives the first and last day of its period. |
| A year → month → day drilldown | Grouping by the `__year` and `__month` lookups. A year-only value stays at the year level and never appears under a month. |

Two things Django provides for date columns are lost:

- The admin's `date_hierarchy`, which accepts only `DateField` and `DateTimeField`.
- Django's own `Trunc` and `Extract` functions, which this package replaces with its own lookups.

### Performance

Measured on PostgreSQL 17 with 5,000,000 rows, a third each at year, month and day precision,
1900–2025, one B-tree index on each column, no parallel workers. A timestamp column stored the
way the replaced package stored it is the baseline.

| Query | Timestamp | ISO string |
|---|---|---|
| June 1990 to 23 September 2020 (1.2 million matches) | 303 ms | 310 ms |
| One month (2,126 matches) | 10 ms | 10 ms |
| The year 2020 | 98 ms | 105 ms |
| June of any year | 1,297 ms | 933 ms |
| Counts per month across a decade | 309 ms | 325 ms |
| Counts per year across the whole table | 1,715 ms | 1,445 ms |
| The newest 50 | 0.4 ms | 0.3 ms |
| The first day of every row | 964 ms | 2,177 ms |
| Size on disk, with the index | 207 MB | 220 MB |

- Range, year and sorting queries use the index in both.
- "June of any year" reads the whole table in both, as Django's `__month` does on a real date.
- Converting every row to a real date is the one slower case. A chart that groups by month or
  year first does not pay it.
- The string column was measured with the `C` collation. A prefix match such as
  `LIKE '2020%'` uses the index only when the column compares text byte by byte. Under a
  language-aware collation such as `en_US.UTF-8`, PostgreSQL cannot use the index for it. Byte
  order is also the only order guaranteed to match date order. This is why the field sets the
  `C` collation itself. SQLite compares byte by byte by default.

## Revisit if

- Uncertain or approximate dates come into scope (`1990?`, `1990~`). The column holds them
  without a schema change, but text order would no longer match date order.
- Years before 0001 or after 9999 are needed. Text order breaks once a year is not four digits.
- A backend this package supports cannot use an index for a prefix range on a text column.