Calculating a date in Postgres by adding months?

Question

I have a postgres table that has the following fields

start_date,duration

duration contains any number of months, so to calculate the end date you add the months in duration to the start date. Now I want to do something like this with a postgres query.

SELECT * 
FROM table 
WHERE start_date > '2010-05-12' 
AND (start_date + duration) < '2010-05-12'

Is this possible and how does one right the syntax?

The version of my postgres is PostgreSQL 8.1.22 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48)

Answer 1

try:

(start_date + (duration * '1 month'::INTERVAL)) < '2010-05-12'

or

(start_date + (duration || ' month')::INTERVAL) < '2010-05-12'

More info: Date/Time Functions and Operators