Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

postgresql - Calculating a date in Postgres by adding months?

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)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...