CODEWITHSUNDEEP
postgresql
psrpsrpsr
psrpsrpsr

Reputation: 457

How can I calculate the number of months between YYYYMM integer values in PostgreSQL?

How can I calculate the number of months between two YYYYMM integer values in Postgresql?

DATA:

| Date1  | Date2  |
|--------|--------|
| 201608 | 201702 |
| 201609 | 201610 |

DESIRED OUTPUT:

| Date1  | Date2  | MonthsBetweenInclusive | MonthsBetweenExclusive |
|--------|--------|------------------------|------------------------|
| 201608 | 201702 | 7                      | 6                      |
| 201609 | 201610 | 2                      | 1                      |

I have looked at the PostgreSQL date function documentation but I'm unable to find a solution that operates on YYYYMM integer values.

Upvotes: 1

Views: 509

Answers (5)

Abelisto
Abelisto

Reputation: 15614

with t(d1,d2) as (values(201608,201702),(201609,201610))
select
  *,
  ((d2/100*12)+(d2-d2/100*100))-((d1/100*12)+(d1-d1/100*100))
from t;

Upvotes: 2

light souls
light souls

Reputation: 728

select yt.date1,
   yt.date2,
   trunc(EXTRACT(EPOCH from age(to_timestamp(yt.date2::TEXT, 'YYYYMM'),to_timestamp(yt.date1::TEXT, 'YYYYMM')))/(3600*24*30)),
   trunc(EXTRACT(EPOCH from age(to_timestamp(yt.date2::TEXT, 'YYYYMM'),to_timestamp(yt.date1::TEXT, 'YYYYMM')))/(3600*24*30)) +1
 from your_table yt

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

with t (date1, date2) as (values
    (201608,201702),(201609,201610)
)
select array_length(
    array((
        select generate_series(
            to_date(date1::text, 'YYYYMM'),
            to_date(date2::text, 'YYYYMM'),
            '1 month'
        )
    )), 1
)
from t
;
 array_length 
--------------
            7
            2

Upvotes: 1

phatfingers
phatfingers

Reputation: 10250

SELECT date1, date2, 
1 + extract(year from age(to_timestamp(date1::text,'YYYYMM'),to_timestamp(date2::text,'YYYYMM'))) * 12 
+ extract(month from age(to_timestamp(date1::text,'YYYYMM'),to_timestamp(date2::text,'YYYYMM'))) AS MonthsBetweenInclusive,
extract(year from age(to_timestamp(date1::text,'YYYYMM'),to_timestamp(date2::text,'YYYYMM'))) * 12 
+ extract(month from age(to_timestamp(date1::text,'YYYYMM'),to_timestamp(date2::text,'YYYYMM'))) AS MonthsBetweenExclusive
FROM datetable;

Upvotes: 0

Pavel Stehule
Pavel Stehule

Reputation: 45770

There are more ways - what is correct, depends on your case:

select extract( months from (justify_interval(to_timestamp('201610','YYYYMM') -
                                              to_timestamp('201609','YYYYMM'))));
┌───────────┐
│ date_part │
╞═══════════╡
│         1 │
└───────────┘
(1 row)

or

CREATE OR REPLACE FUNCTION month_number(date)
RETURNS int AS $$
  SELECT ((EXTRACT(year FROM $1) - 1900) * 12 +
          EXTRACT(month FROM $1))::int
$$ LANGUAGE sql;

SELECT month_number(to_date('201702','YYYYMM')) - 
       month_number(to_date('201608','YYYYMM'));
┌──────────┐
│ ?column? │
╞══════════╡
│        6 │
└──────────┘
(1 row)

or

SELECT (to_date('201702','YYYYMM') - 
        to_date('201608','YYYYMM'))/30;
┌──────────┐
│ ?column? │
╞══════════╡
│        6 │
└──────────┘
(1 row)

Upvotes: 1

Related Questions