Months between two dates function

Question

In oracle i can find out no:of months between using MONTHS_BETWEEN function.

In postgres i am using extract function for this. eg.like

select 
    extract(year from age(current_date, '2012-12-09')) * 12
    + 
    extract(month from age(current_date, '2012-12-09'))

Is there any other ways(built in functions) in postgres??

even i used the same way – Angelin Nadar Oct 09 '14 at 12:24 — Angelin Nadar, Oct 09 '14 at 12:24

score 10 · Answer 1 · answered Jan 10 '13 at 12:49

10

This is easy to re-implement in PostgreSQL just using SQL functions to tidy up what you've already got:

create function months_of(interval)
 returns int strict immutable language sql as $$
  select extract(years from $1)::int * 12 + extract(month from $1)::int
$$;

create function months_between(date, date)
 returns int strict immutable language sql as $$
   select abs(months_of(age($1, $2)))
$$;

And now select months_between('1978-06-20', '2011-12-09') produces 401.

answered Jan 10 '13 at 12:49

araqnid

127,052
24
157
134

6

"Month" is a fuzzy term, and unfortunately it doesn't get less fuzzy when you wrap a function around it. Using `months_between('2012-01-01', '2012-01-31')` returns 0 for that 30-day interval. But using `months_between('2012-02-01', '2012-03-01')` returns 1 for that 29-day interval. The short story is that you *ought* to define what "month" means in your application before you start writing code. – Mike Sherrill 'Cat Recall' Jan 10 '13 at 13:01
1

@Catcall Your comment is quite valid but shouldn't it be directed at the OP? araqnid's code behaves exactly as the OP's code does so I suppose that is what he wants. – Clodoaldo Neto Jan 10 '13 at 13:18
4

I don't know. I upvoted araqnid's answer. In my experience, most people who use functions like this *don't* think about what *month* or *week number* ought to mean in their application. They just use whatever they find. (That's an observation, not a criticism.) The OP hasn't accepted an answer, so I expect him or her to read all this stuff sooner or later. Maybe I'm unrealistically optimistic. – Mike Sherrill 'Cat Recall' Jan 10 '13 at 14:03
@Catcall Good points, and I agree that the OP needs to figure out/guess how this needs to be defined for his situation. – araqnid Jan 10 '13 at 15:08

Bohemian · Answer 2 · 2013-01-10T07:02:46.790

3

Unfortunately it seems not, because extract(month ...) returns the number of months modulo 12.

There is one small simplification you can make; remove the first parameter of age() - the default is age from current_date, so these two are equivalent:

age(current_date, '2012-12-09')
age('2012-12-09')

edited Jan 10 '13 at 07:02

answered Jan 10 '13 at 06:27

Bohemian

412,405
93
575
722

`age('2012-12-09')` will produce an error (postgresql 9.2.1) due to the unknown argument type. You need to specify `age('2012-12-09'::date)` – araqnid Jan 10 '13 at 12:41

score 1 · Answer 3 · answered Jan 10 '13 at 10:57

You can use UDF, e.g. I've found the following here:

   CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP) 
     RETURNS INT AS $$
   DECLARE
     diff_interval INTERVAL; 
     diff INT = 0;
     years_diff INT = 0;
   BEGIN
     IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
       years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);

       IF units IN ('yy', 'yyyy', 'year') THEN
         -- SQL Server does not count full years passed (only difference between year parts)
         RETURN years_diff;
       ELSE
         -- If end month is less than start month it will subtracted
         RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t)); 
       END IF;
     END IF;

     -- Minus operator returns interval 'DDD days HH:MI:SS'  
     diff_interval = end_t - start_t;

     diff = diff + DATE_PART('day', diff_interval);

     IF units IN ('wk', 'ww', 'week') THEN
       diff = diff/7;
       RETURN diff;
     END IF;

     IF units IN ('dd', 'd', 'day') THEN
       RETURN diff;
     END IF;

     diff = diff * 24 + DATE_PART('hour', diff_interval); 

     IF units IN ('hh', 'hour') THEN
        RETURN diff;
     END IF;

     diff = diff * 60 + DATE_PART('minute', diff_interval);

     IF units IN ('mi', 'n', 'minute') THEN
        RETURN diff;
     END IF;

     diff = diff * 60 + DATE_PART('second', diff_interval);

     RETURN diff;
   END;
   $$ LANGUAGE plpgsql;

I recently worked with a new developer that included this function to ours databases to do some jobs and I can testify that SUCKS, it is extremely slow, no optimized, the performing is so badly. It can be multi-purpose, but is not a good solution. Just try this simple comparison: age(birthdate) against this function. — FiruzzZ, Apr 06 '16 at 11:13

score 0 · Answer 4 · answered Jun 12 '15 at 18:29

0

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

answered Jun 12 '15 at 18:29

atiruz

2,782
27
36

Months between two dates function

4 Answers4

Linked