Difference in months of date in postgresql

6

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

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

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

But what I need is total in months, ie,% months of difference. Is there any way?

    
asked by anonymous 15.08.2014 / 01:44

3 answers

6

There is in PostgreSQL the function Age which, in my opinion, represents the difference between the dates. The SQL below also shows the result of difference of months 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

    
15.08.2014 / 02:59
6

You can get the value in months by extracting the parts manually and doing the math operations involved. Unfortunately, PostgreSQL does not have a DATEDIFF function to simplify these calculations:

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

Sources:

15.08.2014 / 01:58
1

If only the integer number of months is required:

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
    
30.09.2014 / 15:00