PostgreSQL Datetime Field Overflow (SQLSTATE 22008)

PostgreSQL raises ERROR: datetime field value out of range with SQLSTATE 22008 and condition name datetime_field_overflow when a date, time, timestamp, or interval value contains a component that falls outside its permitted bounds — for example, an hour value of 25, a month of 13, or an interval exceeding PostgreSQL's storage limits.

What This Error Means

SQLSTATE 22008 belongs to error class 22, "Data Exception." This class covers a wide range of errors related to invalid data values — including division by zero (22012), null value violations in certain contexts, and numeric overflow. The datetime_field_overflow condition specifically indicates that a datetime-related field component (year, month, day, hour, minute, second, or a sub-second fraction) is out of range for its type.

PostgreSQL enforces strict boundaries on date and time types. A timestamp must fall between 4713 BC and 294276 AD. A time value must be between 00:00:00 and 24:00:00. An interval field can hold very large values but individual components (like hours extracted from an interval) are still bounded when you request them explicitly. When any of these boundaries are violated, PostgreSQL raises 22008 and aborts the current statement.

After this error, the current transaction is left in an aborted state. Any subsequent statements will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue a ROLLBACK or ROLLBACK TO SAVEPOINT. The connection itself remains open and usable after the rollback.

Common Causes

1. Constructing timestamps from out-of-range parts using make_timestamp or make_date. Calling make_date(2024, 13, 1) (month 13) or make_time(25, 0, 0) (hour 25) directly produces 22008.

2. Arithmetic that pushes a timestamp beyond PostgreSQL's supported range. Adding a very large interval to a timestamp near the maximum value (294276-12-31) can overflow the timestamp type.

3. Extracting or truncating interval values where the result overflows an integer. Using EXTRACT(EPOCH FROM very_large_interval) can produce a value that overflows the underlying numeric representation.

4. Inserting date/time strings that an implicit cast interprets as having invalid fields. A string like '2024-04-31' (April has 30 days) does not produce 22008 — PostgreSQL raises a different error for that — but strings with explicitly invalid field values (e.g., constructing via format functions) can trigger it.

5. Application-side date arithmetic passing incorrect values. ORMs or application code computing a future/past date by multiplying user input without bounds-checking can produce timestamps outside PostgreSQL's representable range.

6. Using INTERVAL syntax with values that exceed field limits. For example, INTERVAL '2147483648 months' can overflow the internal month storage.

How to Fix datetime_field_overflow

1. Validate inputs before passing them to PostgreSQL. Check that year, month, day, hour, minute, and second values are within their valid ranges in your application layer before building SQL.

   -- Instead of this (may produce 22008 if month=13):
   SELECT make_date(2024, 13, 1);
   
   -- Validate first, or use a safe construction approach:
   SELECT make_date(2024, 12, 1);
   

2. Clamp or reject out-of-range timestamps before inserting. If your application generates timestamps from arithmetic, add guards:

   -- Check whether a computed timestamp is in range before using it:
   SELECT CASE
     WHEN computed_ts BETWEEN '-4713-01-01 BC'::timestamp AND '294276-12-31'::timestamp
     THEN computed_ts
     ELSE NULL
   END;
   

3. Use EXTRACT carefully with large intervals. If you need epoch seconds from a large interval and might overflow, cast through numeric rather than relying on integer arithmetic:

   -- Safer extraction for large intervals:
   SELECT EXTRACT(EPOCH FROM my_interval)::numeric;
   

4. Check interval literal syntax. Confirm that interval literals in your SQL stay within bounds. Break very large intervals into parts or use numeric types for arithmetic before converting back:

   -- This can overflow month storage:
   -- SELECT INTERVAL '2147483648 months';
   
   -- Use numeric arithmetic instead:
   SELECT (2147483648::numeric * INTERVAL '1 month');
   

5. Rollback the current transaction after the error. Since 22008 aborts the current statement and leaves the transaction in an error state, always issue a rollback before retrying:

   ROLLBACK;
   -- or, if using savepoints:
   ROLLBACK TO SAVEPOINT my_savepoint;
   

6. In PL/pgSQL, catch the exception and handle it. Use an EXCEPTION block with WHEN datetime_field_overflow to handle the error gracefully inside stored procedures:

   DO $$
   BEGIN
     INSERT INTO events(event_time) VALUES (make_timestamp(2024, 13, 1, 0, 0, 0));
   EXCEPTION
     WHEN datetime_field_overflow THEN
       RAISE NOTICE 'Invalid datetime value, skipping row';
   END;
   $$;
   

Additional Information

• PostgreSQL's timestamp type covers 4713 BC to 294276 AD; timestamptz has the same range. Exceeding these limits raises 22008.
• Related SQLSTATE codes in class 22 include: 22003 (numeric_value_out_of_range), 22007 (invalid_datetime_format), and 22009 (invalid_time_zone_displacement_value). If the problem is a date/time format issue rather than an out-of-range field, you will see 22007 instead of 22008.
• JDBC drivers typically surface this as a PSQLException with getSQLState() returning "22008".
• SQLAlchemy raises sqlalchemy.exc.DataError wrapping the original PSQLException; the orig attribute contains the SQLSTATE.
• Most ORMs (Django ORM, ActiveRecord, Hibernate) will propagate this as a generic database error or data error exception — check the cause/wrapped exception for the SQLSTATE to distinguish it from other data errors.
• Timezone conversions can amplify overflow risks: a timestamp valid in UTC may appear to overflow when shifted to a timezone with a large positive offset. Ensure timestamps are stored and compared in a consistent timezone.

Frequently Asked Questions

Why does PostgreSQL raise 22008 instead of silently clamping the value? PostgreSQL follows the SQL standard strictly for data exceptions. Silently clamping an out-of-range datetime value would allow incorrect data to be stored without any warning, which could corrupt business logic or reporting. The explicit error forces the application to handle the invalid input intentionally.

Is 22008 the same as "date out of range" errors I sometimes see? Not exactly. PostgreSQL has several related date/time errors. 22007 covers format parsing failures (e.g., passing a non-date string where a date is expected). 22008 specifically covers cases where the value is structurally valid but a field component exceeds its bounds. Some drivers and error messages use the phrase "date out of range" loosely, so always check the SQLSTATE to be certain.

Can an interval value trigger 22008? Yes. While interval types can represent very large durations, individual field components have internal storage limits. Constructing an interval with an excessively large month or year component, or performing arithmetic that overflows the internal representation, can raise 22008.

After getting this error, do I need to reconnect to the database? No. The connection stays open. You only need to roll back the aborted transaction (ROLLBACK) before issuing new statements. Reconnecting is unnecessary and wasteful.