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)

8.1 ? You should really upgrade ! If "duration" is of type INTERVAL then it will work, if it's an integer number of months you have to do "duration * '1 MONTH'::INTERVAL". — bobflux
– bobflux, Commented May 6, 2011 at 9:40
@peufeu - 8.1 is the default version on RHEL5. It is stable and will be supported by Red Hat until at least 2014. In some cases that matters a lot more than having the latest version. — user533832
– user533832, Commented May 7, 2011 at 18:34

manji · Accepted Answer · 2011-05-06 09:59:58Z

19

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

edited May 6, 2011 at 9:59

answered May 6, 2011 at 9:37

manji

48k5 gold badges98 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elitmiar Over a year ago

= Getting Syntax ERROR: syntax error at or near "duration" at character 220

Community · Accepted Answer · 2017-05-23 12:15:03Z

1

You might find this article useful: PostgreSQL: month := interval '30 days';

Also this link: http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL, more specifically the section called "WORKING with DATETIME, DATE, and INTERVAL VALUES".

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered May 6, 2011 at 9:32

Arj

2,0675 gold badges27 silver badges46 bronze badges

Collectives™ on Stack Overflow

Calculating a date in Postgres by adding months?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related