Time & dates
Concept  ·  SQL & Querying

Adding months clamps to month-end when the day doesn't exist (Jan 31 -> Feb 28), and clamping doesn't undo — plus one month twice is not plus two months.

'One month later' has no single answer on the calendar's ragged edge, so engines clamp (Jan 31 -> Feb 28) — reasonable, silent, and lossy: the clamped date forgets it was ever the 31st, so iterating month-hops walks anniversary dates permanently down to the 28th, while a single jump preserves them. Every subscription renewal and billing anchor lives on this edge.

See it break
anchor
d
2025-01-31
The trap
SELECT
  strftime(d + INTERVAL 1 MONTH + INTERVAL 1 MONTH, '%Y-%m-%d') AS renewal
FROM
  anchor;
The fix
SELECT
  strftime(d + INTERVAL 2 MONTH, '%Y-%m-%d') AS renewal
FROM
  anchor;
The trap
2025-03-28
The fix
2025-03-31

The subscription anchored Jan 31 decayed to the 28th — adding a month clamps Jan 31 to Feb 28, and the clamp is lossy, so hopping month by month walks the date permanently down; a single plus-two-months from the anchor keeps Mar 31.

The rule

Compute each occurrence from the original anchor (LEAST(anchor_day, days_in_month)); spell month-end intent as month-end (last_day()).

Shows up elsewhere
Half-open intervalsBETWEEN on timestamps — the midnight trap
Try one →Prove it in practice →