How to fix Datetime field overflow in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 251 views

PostgreSQL throws datetime field overflow (SQLSTATE 22008) when a date or time value is outside the valid range for its field. Common causes include invalid dates like "0000-00-00 00:00:00", incorrect formats, or timestamps with excessive precision.

Quick fix

Fixes Datetime field overflow

ERROR: date/time field value out of range: "0000-00-00 00:00:00"
  1. Identify the problematic query and column
  2. Check the date value being inserted
  3. Use NULL instead of zero dates
  4. Convert Unix timestamps to proper datetime format
postgresql — error
ERROR: date/time field value out of range: "0000-00-00 00:00:00"
Datetime field overflow

What this error means

PostgreSQL enforces strict validation on date and time values to maintain data integrity. When you attempt to insert, update, or convert a date/time value that falls outside the valid range for that field, PostgreSQL raises error 22008 (datetime_field_overflow). This differs from some other databases that might coerce invalid dates forward—PostgreSQL simply rejects them. Valid DATE values range from 4713 BC to 5874897 AD, but common issues occur with impossible dates like month 13, day 32, or February 29 on non-leap years.

Typical symptoms

  • SQLSTATE[22008] error when inserting or updating timestamp columns
  • Application crashes with "date/time field value out of range" message
  • Queries fail during data migration from MySQL or other databases
  • Timestamps with excessive precision (nanoseconds) are rejected
  • Default values like "0000-00-00 00:00:00" cause insertion failures

Common causes

  1. Invalid zero date value (0000-00-00 00:00:00) commonly from MySQL migrations
  2. Day and month values swapped or out of valid range (month > 12, day > 31)
  3. Timestamps with precision beyond microseconds (PostgreSQL supports up to microseconds only)
  4. February 29 inserted on non-leap years
  5. Unix timestamps or millisecond values used directly without conversion
  6. Wrong datestyle setting causing misinterpretation of date components

How to fix "Datetime field overflow"

1Identify the problematic query and column

Check your application logs to find the exact query causing the error. Note which column is referenced in the error message.

Example error:

bash
ERROR: date/time field value out of range: "0000-00-00 00:00:00"
2Check the date value being inserted

Examine the actual data being inserted or the SQL query. Look for:

- Zero dates: "0000-00-00 00:00:00"
- Invalid components: month > 12, day > 31
- February 29 on non-leap years
- Timestamps in milliseconds or nanoseconds

Example problem query:

sql
INSERT INTO users (created_at) VALUES ('0000-00-00 00:00:00');
3Use NULL instead of zero dates

Replace "0000-00-00 00:00:00" with NULL for empty timestamp fields. PostgreSQL requires NULL for missing values.

Before:

sql
INSERT INTO users (created_at) VALUES ('0000-00-00 00:00:00');

After:

sql
INSERT INTO users (created_at) VALUES (NULL);

In your application code:

javascript
const createdAt = dateValue === '0000-00-00 00:00:00' ? null : dateValue;
4Convert Unix timestamps to proper datetime format

If inserting Unix timestamps (seconds or milliseconds since epoch), convert them to PostgreSQL timestamp format first.

For Unix timestamp in seconds:

sql
SELECT to_timestamp(1678513615);
-- Returns: 2023-03-11 09:06:55+00:00

For Unix timestamp in milliseconds, divide by 1000:

sql
SELECT to_timestamp(1678513615000 / 1000.0);

In your ORM or application:

javascript
const timestamp = new Date(unixTimestampMs);
const pgFormat = timestamp.toISOString(); // '2023-03-11T09:06:55.000Z'
5Ensure date components are in valid ranges

PostgreSQL uses YYYY-MM-DD format for dates. Validate before inserting:

- Year: any reasonable value (4713 BC to 5874897 AD)
- Month: 1-12
- Day: 1-31 (depending on month)
- Hour: 0-23
- Minute/Second: 0-59

Example validation in JavaScript:

javascript
const date = new Date('2024-02-30'); // Invalid
if (!Number.isNaN(date.getTime())) {
  // Date is valid
} else {
  // Handle invalid date
}

In SQL, check leap years for February 29:

sql
SELECT '2024-02-29'::date; -- Valid (2024 is leap year)
SELECT '2023-02-29'::date; -- ERROR (2023 is not leap year)
6Check and adjust datestyle if needed

PostgreSQL interprets ambiguous dates based on the datestyle setting. Check current setting:

sql
SHOW datestyle;
-- Returns: ISO, MDY (or similar)

If dates are being misinterpreted, adjust the setting:

sql
SET datestyle = 'ISO, MDY';
-- or
SET datestyle = 'ISO, DMY';

For permanent change, update postgresql.conf or use in connection string:

bash
PostgreSQL://user:pass@host/db?options=-c%20datestyle%3DISO,MDY

Always use ISO format (YYYY-MM-DD) in applications to avoid ambiguity.

Advanced notes

PostgreSQL stores timestamps as 8-byte integers (since version 8.4), measuring microseconds since epoch. This limits the valid range to approximately 4713 BC to 5874897 AD. Unlike MySQL, which coerces invalid dates to zero dates or rounds them forward, PostgreSQL strictly validates dates on insert/update. When migrating from MySQL, systematically replace "0000-00-00 00:00:00" values with NULL. For timezone-aware applications, use TIMESTAMP WITH TIME ZONE. Always validate dates at the application layer before hitting the database to provide better error messages to users. If debugging corrupted timestamps in existing data, use binary-level queries to identify timestamps falling outside valid ranges.

Frequently asked questions

What does "Datetime field overflow" mean?
PostgreSQL enforces strict validation on date and time values to maintain data integrity. When you attempt to insert, update, or convert a date/time value that falls outside the valid range for that field, PostgreSQL raises error 22008 (datetime_field_overflow). This differs from some other databases that might coerce invalid dates forward—PostgreSQL simply rejects them.
How do I fix "Datetime field overflow"?
PostgreSQL throws datetime field overflow (SQLSTATE 22008) when a date or time value is outside the valid range for its field. Common causes include invalid dates like "0000-00-00 00:00:00", incorrect formats, or timestamps with excessive precision.
What causes "Datetime field overflow"?
Invalid zero date value (0000-00-00 00:00:00) commonly from MySQL migrations Day and month values swapped or out of valid range (month > 12, day > 31) Timestamps with precision beyond microseconds (PostgreSQL supports up to microseconds only)
Is the "Datetime field overflow" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading