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:

- 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” **EXTRACT**the number of years from the interval. In the above example, this would be 1.- 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)
**EXTRACT**the number of months from the interval. In the above example, this would be 2.- 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.

Just a moment to express my thanks.

I am new at Postgre – one week.

Thank You.

Ken

Very glad it helped! Let me know if you come across any coding conundrums of your own that you’d like some help with.

It helped me as well, after going through many docs.

Regards,

Filipe

Hi,

Just to keep in sync with Oracle’s definition you can add something list EXTRACT(day FROM age(end_date, start_date)) / 31 to get the decimal part representing the number of days between the two date relative to a 31 days month.

Regards,

Adrian

That was really helpful.. you saved the day for me .Thanx

Working with months in PG is certainly ugly.

This might be useful to someone; If you happen to have a date or calendar dimension/lookup – adding an ‘absolute_month_number’ column can be very helpful.

e.g.

select a.month_absolute_no – b.month_absolute_no

from dw_mart_base.dim_date a

, dw_mart_base.dim_date b

where a.month_sk = ‘2019-08-01’

and b.month_sk = ‘2018-08-01’;

–12 expected