Count months between two timestamp on postgresql?

Question

I want to count the number of months between two dates.

Doing :

SELECT TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40';

Returns : 0 years 0 mons 409 days 20 hours 0 mins 0.00 secs

and so:

SELECT extract(month from TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40');

returns 0.

Answer 1

This work very well(leap year is not resolved in this solution):

(EXTRACT(YEAR FROM AGE(date1, date2))*12) + (EXTRACT(MONTH from AGE(date1,CAST(date2 AS DATE) + CAST(EXTRACT(YEAR FROM AGE(date1, date2)) AS INT) * 365))) 

Months From Full Years + The Remaining Months

Answer 2

Try this (PostgreSQL):

    select abs( (extract(year from date1)*12+extract(month from date1)) - (extract(year from date2)*12+extract(month from date2) ) ) as "months_betw"
from(select timestamp '2012-07-01 01:15:00' as date2 , timestamp '2013-01-15 15:05:00' as date1 )as dates

We will multiply date's year by 12 and add the value of the month to it (for each date separately enclosed with parentheses)

(extract(year from date1)*12+extract(month from date1)) 

We now have months represntation for each date's (years and months) as integers

('2012-07' = 24151) date1 and ('2013-01' = 24157) date2

We can now get diff by getting the absolute of there subtraction

|(24151)-(24157)| = 6 "the difference between the two dates"

Answer 3

Here is a PostgreSQL function with the exact same behavior as the Oracle MONTHS_BETWEEN function. It has been tested on a wide range of years (including leap ones) and more than 700k combinations of dates (including end of every months).

CREATE OR REPLACE FUNCTION months_between
(   DATE,
    DATE
)
RETURNS float
AS
$$
    SELECT
            (EXTRACT(YEAR FROM $1) - EXTRACT(YEAR FROM $2)) * 12
        +   EXTRACT(MONTH FROM $1) - EXTRACT(MONTH FROM $2)
        +   CASE
                WHEN    EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM LAST_DAY($2))
                    AND EXTRACT(DAY FROM $1) = EXTRACT(DAY FROM LAST_DAY($1))
                THEN
                    0
                ELSE
                    (EXTRACT(DAY FROM $1) - EXTRACT(DAY FROM $2)) / 31
            END
    ;
$$
LANGUAGE SQL
IMMUTABLE STRICT;

This function requires a LAST_DAY function (behaving the same as Oracle's one) :

CREATE OR REPLACE FUNCTION last_day
(   DATE
)
RETURNS DATE
AS
$$
    SELECT
        (DATE_TRUNC('MONTH', $1) + INTERVAL '1 MONTH' - INTERVAL '1 DAY')::date
    ;
$$
LANGUAGE SQL
IMMUTABLE STRICT;

Answer 4

I made a function like this:

/* similar to ORACLE's MONTHS_BETWEEN */
CREATE OR REPLACE FUNCTION ORACLE_MONTHS_BETWEEN(date_from DATE, date_to DATE)
RETURNS REAL LANGUAGE plpgsql
AS
$$
DECLARE age INTERVAL;
declare rtn real;
BEGIN
    age := age(date_from, date_to);
    rtn := date_part('year', age) * 12 + date_part('month', age) + date_part('day', age)/31::real;
    return rtn;
END;
$$;

Oracle Example)

SELECT MONTHS_BETWEEN
(TO_DATE('2015-02-02','YYYY-MM-DD'), TO_DATE('2014-12-01','YYYY-MM-DD') ) 
"Months" FROM DUAL;
--result is: 2.03225806451612903225806451612903225806

My PostgreSQL function example)

select ORACLE_MONTHS_BETWEEN('2015-02-02'::date, '2014-12-01'::date) Months;
-- result is: 2.032258

From the result you can use CEIL()/FLOOR() for rounding.

select ceil(2.032258)  --3
select floor(2.032258) --2

Answer 5

age function returns interval:

age(timestamp1, timestamp2)

Then we try to extract year and month out of the interval and add them accordingly:

select extract(year from age(timestamp1, timestamp2)) * 12 +
extract(month from age(timestamp1, timestamp2))

Answer 6

Extract by year and months will floor on months:

select extract(year from age('2016-11-30'::timestamp, '2015-10-15'::timestamp)); --> 1
select extract(month from age('2016-11-30'::timestamp, '2015-10-15'::timestamp)); --> 1
--> Total 13 months

This approach maintains fractions of months (thanks to tobixen for the divisor)

select round(('2016-11-30'::date - '2015-10-15'::date)::numeric /30.43, 1); --> 13.5 months

Answer 7

Please note that the most voted answer by @ram and @angelin is not accurate when you are trying to get calendar month difference using.

select extract(year from age(timestamp1, timestamp2))*12 + extract(month from age(timestamp1, timestamp2))

for example, if you try to do:

select extract(year from age('2018-02-02'::date, '2018-03-01'::date))*12 + extract(month from age('2018-02-02'::date , '2018-03-01'::date))

the result will be 0 but in terms of months between March from February should be 1 no matter the days between dates.

so the formula should be like the following saying that we start with timestamp1 and timestamp2:

((year2 - year1)*12) - month1 + month2 = calendar months between two timestamps

in pg that would be translated to:

select ((extract('years' from '2018-03-01 00:00:00'::timestamp)::int -  extract('years' from '2018-02-02 00:00:00'::timestamp)::int) * 12) 
    - extract('month' from '2018-02-02 00:00:00'::timestamp)::int + extract('month' from '2018-03-01 00:00:00'::timestamp)::int;

you can create a function like:

CREATE FUNCTION months_between (t_start timestamp, t_end timestamp)
RETURNS integer
AS $$
select ((extract('years' from $2)::int -  extract('years' from $1)::int) * 12) 
    - extract('month' from $1)::int + extract('month' from $2)::int
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Answer 8

SELECT floor(extract(days from TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP
'2011-04-30 14:38:40')/30.43)::integer as months;

Gives an approximate value but avoids duplication of timestamps. This uses hint from tobixen's answer to divide by 30.43 in place of 30 to be less incorrect for long timespans while computing months.

Answer 9

If you will do this multiple times, you could define the following function:

CREATE FUNCTION months_between (t_start timestamp, t_end timestamp)
    RETURNS integer
    AS $$
        SELECT
            (
                12 * extract('years' from a.i) + extract('months' from a.i)
            )::integer
        from (
            values (justify_interval($2 - $1))
        ) as a (i)
    $$
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

so that you can then just

SELECT months_between('2015-01-01', now());

Answer 10

SELECT date_part ('year', f) * 12
      + date_part ('month', f)
FROM age ('2015-06-12', '2014-12-01') f

Result: 6 Months

Answer 11

I had the same problem once upon a time and wrote this ... it's quite ugly:

postgres=>  SELECT floor((extract(EPOCH FROM TIMESTAMP '2012-06-13 10:38:40' ) - extract(EPOCH FROM TIMESTAMP '2005-04-30 14:38:40' ))/30.43/24/3600);
 floor 
-------
    85
(1 row)

In this solution "one month" is defined to be 30.43 days long, so it may give some unexpected results over shorter timespans.

Answer 12

The age function give a justified interval to work with:

SELECT age(TIMESTAMP '2012-06-13 10:38:40', TIMESTAMP '2011-04-30 14:38:40');

returns 1 year 1 mon 12 days 20:00:00, and with that you can easily use EXTRACT to count the number of months:

SELECT EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) AS months_between
FROM age(TIMESTAMP '2012-06-13 10:38:40', TIMESTAMP '2011-04-30 14:38:40') AS t(age);

Answer 13

Try this solution:

SELECT extract (MONTH FROM age('2014-03-03 00:00:00'::timestamp, 
'2013-02-03 00:00:00'::timestamp)) + 12 * extract (YEAR FROM age('2014-03-03   
00:00:00'::timestamp, '2013-02-03 00:00:00'::timestamp)) as age_in_month;

Answer 14

Try;

select extract(month from  age('2012-06-13 10:38:40'::timestamp, '2011-04-30 14:38:40'::timestamp)) as my_months; 

Answer 15

Gives the differenece of months of two dates

   SELECT ((extract( year FROM TIMESTAMP '2012-06-13 10:38:40' ) - extract( year FROM TIMESTAMP '2011-04-30 14:38:40' )) *12) + extract(MONTH FROM TIMESTAMP '2012-06-13 10:38:40' ) - extract(MONTH FROM TIMESTAMP '2011-04-30 14:38:40' );

The Result : 14

Have to extract months seperately for both the dates and then the difference of both the results