One of the most basic calculations on dates is to tell the time elapsed between two dates. Often it is more helpful to show the date as a number of months rather than a number of days. In PostgreSQL, subtracting one date from another will yield a number of days that then takes a tremendously complex formula to manipulate into months.

The best way I have found to get around this is to use the built in AGE function. The age function calculates the difference between two dates and returns an interval. This may not seem like we’ve gotten very far in calculating the number of months between the two dates, but stick with me on this one. You can then EXTRACT the pieces of the interval needed to calculate months and use them in a simple equation.

EXTRACT(year FROM age(end_date,start_date))*12 + EXTRACT(month FROM age(end_date,start_date))

Here’s the step by step:

1. Use the AGE function to calculate the difference between the end date and start date. Note that it is subtracting the second from the first, so you must have them in this order. This will return an interval that may look something like “1 year 2 months 3 days”
2. EXTRACT the number of years from the interval. In the above example, this would be 1.
3. Multiply the number of years by 12 to get the number of months counted by those years (seems obvious, but I am outlining all the steps in excruciating detail)
4. EXTRACT the number of months from the interval. In the above example, this would be 2.
5. Add the two together. In the above example, this will yield 14.

You can then cast this as whatever you’d like, do additional calculations, etc.

Caveats

• It helps to use DATE_TRUNC on the dates you are using to standardize them if you don’t need to be uber-precise. Using a string like the following will push the date or timestamp to the start of the month.

  date_trunc('month',start_date)

• This can also be used to calculate quarters or fiscal quarters or whatever… but that’s a question for another post.

P.S. This method works in Netezza as well.