postgresql date diff between two date with two table and two fields

Question

I have one table with name tblprop and data:

houseid | date      |

--------+------------

 1      | 2014-10-10|

and another table with name tbltrans and data:

transno |houseid | transdate    |

--------+-------- +--------------

 1      |   1     |2014-10-20   |

I want a query that calculates the difference between date of tblprop and transdate of tbltrans and the result is in terms of days.

result is like this:

houseid | difference|

--------+------------

 1      | 10days    |

Thanks and more power.

Answer 1

Just subtract them. As both are dates the result of the difference is the number of days.

select p.houseid,
       t.transdate - p.date
from tblprop p
  join tbltrans t on t.houseid = p.houseid;

If both were timestamp columns you'd get back an interval (rather than an integer representing days)