Calculating months between dates in PostgreSQL

February 4, 2015 · PostgreSQL · SQL

Note: I found this technique on Lexy Kassan’s blog while searching for a way to do date difference calculations purely in PostgreSQL without custom functions. Here’s the link to the original post:

http://www.lexykassan.com/coding-conundrums/calculating-months-between-dates-in-postgresql/

I frequently work with actuarial experience data and need to be able to do ad-hoc grouping based on a combination of columns, or a new calculated column. In this case I wanted to get the age of a vehicle record in months.

EXTRACT(year FROM age(eff_date, incept_date))*12 +
EXTRACT(month FROM age(eff_date, incept_date)) AS maturity

Here, the eff_date is the date when the term began, and the incept_date is the date when the original policy began. All you are doing is letting PostgreSQL find the age between two dates then extracting/combining the year (times 12 months) and months.