Incident brief
date_trunc invalid field
date_trunc() was called with a field name it doesn't recognize. PostgreSQL rejected the call instead of guessing what precision was meant.
What lands in your log
ERROR: unit "fortnight" not recognized for type timestamp without time zone
In 10 seconds
- What triggers it
- Call date_trunc() with a field name that isn't in its accepted list, e.g. 'fortnight'.
- Fix
- Use one of date_trunc()'s accepted field names instead: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, or millennium.
- Proof
- Reproduced on PostgreSQL 16.14 → date_trunc('fortnight', <timestamp>) was rejected with SQLSTATE 22023, because 'fortnight' is not one of date_trunc's accepted field names.
Fix
What to do right now
Application-level steps for this error.
- Use one of date_trunc()'s accepted field names instead: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, or millennium.
- Don't assume a field name that works with EXTRACT() also works with date_trunc(), see the premium counterexample for 'dow'.
SELECT date_trunc('week', TIMESTAMP '2024-03-15 14:30:00');For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
date_trunc invalid field (22023) is statement-local: PostgreSQL does not retain the rejected value after the statement ends. Capture the exact bound value and statement position in application/server logs, then run this targeted validation before retrying.
Test the date_trunc field and input type together
Validate the specific value/shape that can raise this SQLSTATE.
SELECT pg_typeof(now()) AS input_type,
date_trunc('hour', now()) AS valid_example;
-- Replace 'hour' with the exact field sent by the application.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 Functions, date_trunc (9.9.2)
field selects to which precision to truncate the input value. ... Valid values for field are: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.Read the full section on postgresql.org →
The unrecognized field name
Per the manual, date_trunc's field argument only accepts a fixed list of precision names, 'fortnight' isn't one of them, so PostgreSQL raises SQLSTATE 22023 instead of guessing what was meant.Confirming the session still works
date_trunc() rejected the unrecognized field name before evaluating anything else, the session's next statement is unaffected.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.
- 1Call date_trunc() with a field name that isn't in its accepted list, e.g. 'fortnight'.
- 2SQLSTATE 22023 is reported; no value is returned.
One client: date_trunc() called with an unrecognized field name against a fixed literal timestamp.
-- No table needed: the failure happens purely inside date_trunc()'s field argument.
SELECT 'no schema required' AS setup_note;SELECT date_trunc('fortnight', TIMESTAMP '2024-03-15 14:30:00');SELECT 1 AS session_still_alive;What PostgreSQL actually returned
setup_note
--------------------
no schema required
(1 row)ERROR: unit "fortnight" not recognized for type timestamp without time zone session_still_alive
---------------------
1
(1 row)The deeper lab audit for this error
- A field name that works elsewhere, but not here: exact SQL, output, and verdict
- A manual-grounded production interpretation of the lab result
Card required. Cancel before day 7 and you are not charged.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.