Incident brief
Datetime field overflow
A date/time value was well-formed text, but named a value that doesn't exist on the calendar or falls outside PostgreSQL's supported range.
In 10 seconds
- What
- Datetime field overflow
- What triggers it
- Cast a well-formed date string that names an impossible calendar day, such as February 30th.
- The fix
- Validate day-of-month against the actual month and leap-year rules before sending it to PostgreSQL, or just let PostgreSQL reject it and handle the error.
- Proof
- Reproduced on PostgreSQL 16.14 → Casting a well-formed but nonexistent calendar date (February 30th) was rejected with SQLSTATE 22008. The session was unaffected afterward.
The fix
What to do right now
The immediate, application-level response to this error.
- Validate day-of-month against the actual month and leap-year rules before sending it to PostgreSQL, or just let PostgreSQL reject it and handle the error.
- Don't assume a regex/format check is enough — see the premium test for a second, unrelated way this same SQLSTATE is triggered.
- For application-computed dates (e.g. 'add N months'), check the result is still inside PostgreSQL's supported date range (4713 BC to 5874897 AD).
SELECT '2024-02-29'::date; -- 2024 is a leap yearDiagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — Date/Time Types, Table 8.9
date — 4 bytes — date (no time of day) — Low Value: 4713 BC — High Value: 5874897 ADRead the full section on postgresql.org →
The impossible calendar day
PostgreSQL parsed the year, month, and day fields from the text correctly — the problem is that February 2024 (a leap year) only goes up to the 29th, so day 30 doesn't exist. That's a field value out of range, not a parsing failure.Confirming the session still works
The failed cast didn't affect the session — the next statement ran normally.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Cast a well-formed date string that names an impossible calendar day, such as February 30th.
- 2PostgreSQL parses the text fine but finds no such day exists.
- 3SQLSTATE 22008 is reported.
One client: cast a date string that parses fine but names a day that doesn't exist.
-- No table needed: the failure happens purely at the type-cast stage.
SELECT 'no schema required' AS setup_note;SELECT '2024-02-30'::date;SELECT 1 AS session_still_alive;What PostgreSQL actually returned
setup_note
--------------------
no schema required
(1 row)ERROR: date/time field value out of range: "2024-02-30"
LINE 1: SELECT '2024-02-30'::date;
^ session_still_alive
----------------------
1
(1 row)22008 isn't only about impossible calendar days. This was tested directly: a date string that is a perfectly real calendar date, just one that falls outside the date type's supported range entirely.
Cast a date far beyond PostgreSQL's documented maximum (5874897 AD).
Without this
Above: an impossible calendar day (February 30th) is rejected with SQLSTATE 22008.
With this, tested
Below: a real calendar date, just too far in the future for the type, is rejected with the very same SQLSTATE 22008.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Related errors
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed