PostgreSQL DATE Arithmetic (-)

PostgreSQL's DATE arithmetic functions allow you to perform calculations with date values, such as finding the difference between two dates. This functionality is essential for analyzing time intervals, scheduling, and time-based reporting.

Usage

The DATE arithmetic function is employed when you need to compute the difference between two dates or subtract an interval from a date. When two dates are subtracted, the result is an interval, specifically in days. This operation is often used in scenarios like calculating age, duration, or scheduling tasks.

SELECT date1 - date2 AS interval_result;

In this syntax, date1 and date2 are date values, and the subtraction operation - calculates the interval between them.

Examples

1. Basic Date Subtraction

SELECT '2023-10-10'::date - '2023-10-01'::date AS days_difference;

This example calculates the number of days between October 10, 2023, and October 1, 2023, resulting in a difference of 9 days.

2. Subtracting an Interval

SELECT '2023-10-10'::date - INTERVAL '3 days' AS new_date;

Here, the operation subtracts 3 days from October 10, 2023, resulting in a new date of October 7, 2023.

3. Calculating Age from Birthdate

SELECT CURRENT_DATE - birth_date AS age_in_days FROM users;

This example computes the age in days for each user in the users table by subtracting birth_date from the current date.

Tips and Best Practices

• Ensure correct data types. Always cast strings to date type explicitly using ::date for accurate calculations.
• Understand intervals. An interval represents a span of time and can be used for subtracting specific durations, such as days, months, or years, to ensure clarity and maintainability.
• Opt for CURRENT_DATE. Utilize CURRENT_DATE for calculations involving the current date to ensure your queries are dynamic and up-to-date.
• Know your data types. Be aware of the differences between date, timestamp, and timestamp with time zone to avoid confusion, as only timestamp with time zone accounts for time zones.