Difference in postgresql date months

Question

I need to catch the difference of two months dates, for example 16/03/2013 until the date 16/07/2014. If I do

select extract('Month' from age(data1, data2))

What will return is a result 4, as it informs that the difference between the date is 1 year of 4 months.

But what I need is total in months, 15 months apart. There is some way?

Answer 1

You can achieve the value in months by extracting the parts manually and doing the mathematical operations involved. Unfortunately Postgresql does not have a function DATEDIFF to simplify these calculations:

  SELECT (DATE_PART('year', data2) - DATE_PART('year', data1)) * 12 
         + (DATE_PART('month', data2) - DATE_PART('month', data1));

Sources:

• Postgresql - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - Sqlines
• Postgresql Wiki - Working with Dates and Times in Postgresql

Answer 2

Exists in Postgresql a Function Age which in my view portrays well the difference between dates. SQL below also demonstrates the result of months difference between two dates:

SELECT ((ANOS * 12) + MESES) AS MESES
FROM 
(
  SELECT 
    CAST(TO_CHAR(AGE(data2, data1),'YY') AS INTEGER) AS ANOS,
    CAST(TO_CHAR(AGE(data2, data1),'MM') AS INTEGER) AS MESES, 
    CAST(TO_CHAR(AGE(data2, data1),'DD') AS INTEGER) AS DIAS
  FROM tabela
) AS tabela

Sqlfiddle

Answer 3

If it is necessary only the whole number of months:

select
    (
        select count(*) - 1
        from generate_series(date1, date2, '1 month')
    ) as meses
from (values
    ('2013-03-16'::date, '2014-07-16'::date)
) g (date1, date2)
;
 meses 
-------
    16