If you’re getting an error that reads ‘date/time field value out of range‘ in PostgreSQL while using a function such as date_add(), date_subtract(), or date_trunc(), it’s probably because the date value you’re passing to the function is an invalid date.

It’s possible that you’ve got the month and day in the wrong order.

To fix this issue, be sure that you pass a valid date. It may be that all you need to do is switch the day and the month around. Or it could be that you need to change your datestyle setting.

Example of Error

Here’s an example of code that produces the error:

SELECT date_add(
    '23/09/2023',
    '3 day'::interval
    );

Result:

ERROR:  date/time field value out of range: "23/09/2023"
LINE 2:     '23/09/2023',
            ^
HINT:  Perhaps you need a different "datestyle" setting.

This error message provides a hint that suggests we may need to change our datestyle setting.

We get the same error when trying to convert the date/string literal to a date:

SELECT '23/09/2023'::date;

Result:

ERROR:  date/time field value out of range: "23/09/2023"
LINE 1: SELECT '23/09/2023'::date;
               ^
HINT:  Perhaps you need a different "datestyle" setting.

Solution

The solution is to use a valid date. In my case, I can simply switch the day and month around:

SELECT date_add(
    '09/23/2023',
    '3 day'::interval
    );

Result:

        date_add        
------------------------
 2023-09-26 00:00:00+07

Or even better, provide the date using ISO 8601 format:

SELECT date_add(
    '2023-09-23',
    '3 day'::interval
    );

Result:

        date_add        
------------------------
 2023-09-26 00:00:00+07

The datestyle Setting

You can check your datestyle setting with the following command:

SHOW datestyle;

My result:

 DateStyle 
-----------
 ISO, MDY

My result confirms why I got the error. In the first example I formatted the date as DMY when I should have formatted it as MDY. In other words I should have formatted it with the month first, followed by the day, then the year.

By the way, ISO, MDY is the default value for the datestyle setting in PostgreSQL.