Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL DATE Arithmetic (+)

PostgreSQL's date arithmetic functions allow you to perform calculations with date and time values, such as adding or subtracting intervals from dates. These functions are essential for operations that require dynamic date manipulations, like calculating future or past dates.

Usage

Date arithmetic is used when you need to adjust dates by adding or subtracting intervals, such as days, months, or years. The syntax involves using the + or - operator to add or subtract intervals from dates.

sql
SELECT date_column + interval 'value unit';
SELECT date_column - interval 'value unit';

In this syntax, date_column is the date you are adjusting, and 'value unit' specifies the amount and unit of time to add or subtract.

Examples

1. Adding Days to a Date

sql
SELECT '2023-10-15'::date + interval '5 days';

This example adds 5 days to the date 2023-10-15, resulting in 2023-10-20.

2. Subtracting Days from a Date

sql
SELECT '2023-10-15'::date - interval '5 days';

This example subtracts 5 days from the date 2023-10-15, resulting in 2023-10-10.

3. Adding Months to a Date

sql
SELECT '2023-10-15'::date + interval '2 months';

Here, 2 months are added to 2023-10-15, which results in 2023-12-15.

4. Adding Mixed Intervals

sql
SELECT '2023-10-15'::date + interval '1 year 3 months 10 days';

This example adds a combination of 1 year, 3 months, and 10 days to 2023-10-15, resulting in 2025-01-25. PostgreSQL processes these intervals sequentially from left to right.

Tips and Best Practices

  • Use explicit intervals. Clearly define intervals using both the value and unit (e.g., '5 days').
  • Watch for leap years. Be cautious with calculations around leap years, especially when adding years or months.
  • Combine with other date functions. Use date arithmetic in conjunction with other PostgreSQL date functions for complex date manipulations.
  • Test across time zones. If your application operates in multiple time zones, test date arithmetic to ensure consistent results. PostgreSQL handles time zone-aware timestamps by automatically adjusting for time zone differences.