date difference in postgresql

[aneev]

Hi,
I have a table which has 3 fields.

reviewerid varchar(20),
proposalid varchar(20),
dt timestamp without time zone

I would like to know how I can select proposalid's belonging to a particular reviewerid for the span of 12 months?
Is it possible to get the difference in dates when it;s a sigle column? Should I change the datatype to date in which case it would be possible. please suggest. I tried interval but doesn't seem to work.

 

[rycamor]

What do you mean 'I tried interval'? Did you write a query with a self-join, casting the difference between dates to interval and then limiting to under 12 months? (That's how I would do it). If not, then exactly what query did you try?

In fact, INTERVAL columns and/or casting date differences to INTERVAL is truly the way to solve time difference problems in PostgreSQL.

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[aneev]

Hi,
I have not done it the way you exlained. Could you please tell me how I could do self join and extract the rows belonging in the range of only 12 months?

 

[rycamor]

Well, I'm not completely sure of the logic you are trying to achieve here. Actually, I think you don't need a self join at all. If you just want to select everything that falls withing the past 12 months it would look like:

SELECT reviewerid, proposalid, dt, (NOW()-dt)::INTERVAL AS timespan FROM thetable WHERE (NOW() - dt)::INTERVAL <  '12 months'::INTERVAL;

This means "get all rows where the current date minus the stored date is less than 12 months, and show me how old each row is".

But, if you have some other need, please explain further.

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[aneev]

That's great. It works for my requirement. I only have to add few more conditions in that. But it works !! Thank you very much. Since I just started with Postgresql, I couldn't have done this query without proper reading. Thanks for the help. It saved my time. But definitely I need to read and experiment in order to learn.