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.

user330315 · Accepted Answer · 2014-11-18 08:00:54Z

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)

answered Nov 18, 2014 at 8:00

user330315

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

1 Answer 1