Time & dates
Concept  ·  SQL & Querying

date_diff('day', a, b) counts boundary crossings, not elapsed time — two minutes across midnight is 1 day; 23 hours inside one day is 0.

'How many days between' is two different questions wearing one phrase: duration (subtract, get 120 seconds) and calendar distance (count midnights crossed — 1). Both are legitimate; every off-by-one age, tenure, and 'nights stayed' bug is one question answered with the other's tool. Hotel nights are boundary counts; SLAs and durations are subtraction. The discipline: say which in a comment, and test the two-minute-across-midnight row.

See it break
sess
ab
2027-01-01 23:592027-01-02 00:01
The trap
SELECT
  date_diff('day', a, b) AS nights
FROM
  sess;
The fix
SELECT
  date_diff('minute', a, b) AS mins
FROM
  sess;
The trap
1
The fix
2

A two-minute session across midnight billed as a full night — date_diff('day', …) counts boundary crossings (one midnight = 1), not elapsed time; subtraction (or date_diff in minutes) measures the real 2 minutes.

The rule

Subtraction/intervals for durations; date_diff for crossings, named as such; the midnight-straddle row in the fixture, always.

Engines disagree on diff semantics (boundary-count vs truncated duration) — the straddle row is the portable test.

Shows up elsewhere
Half-open intervalsMonth arithmetic clamps and is non-associative
Try one →Prove it in practice →