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

Question

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.

With [modulus and integer division](https://www.postgresql.org/docs/current/static/functions-math.html), maybe? — pozs, Mar 28 '17 at 15:38
convert them to timestamps then use the solution from: http://stackoverflow.com/q/43041943/330315 — , Mar 28 '17 at 15:50

score 2 · Accepted Answer · answered Mar 28 '17 at 17:24

2

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;

answered Mar 28 '17 at 17:24

Abelisto

14,826
2
33
41

This is working, but the math is not clear to me. For the second term (d2-d2/100*100), SQL is outputting 2. When I plug in d2, let's say 201702, I don't get 2 by following the PEMDAS order of operations for (2017-2017/100*100). Is there an implicit order to the way SQL is evaluating these operations that goes against algebraic PEMDAS? – psrpsrpsr Mar 28 '17 at 20:08
@psrpsrpsr The order is: `(201702-((201702/100)*100))`. It uses the usual operators priority and "left-to-right" rule in case if priorities are same. [More in the doc](https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-PRECEDENCE). – Abelisto Mar 28 '17 at 21:21
While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-‌code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Rosário Pereira Fernandes Mar 28 '17 at 21:25
Doesn't (201702-((201702/100)*100)) evaluate to zero? 201702/100 = 2017.02, which is multiplied by 100, so then you have 201702 minus 201702 = 0... am I missing something? I'm certainly no mathematician... – psrpsrpsr Mar 28 '17 at 21:39
1

@psrpsrpsr If both arguments of operator are integer then the result is also integer: `201702/100 = 2017, 2017*100 = 201700, 201702-201700 = 2`. It is not about pure mathematics but about SQL and some other programming languages. – Abelisto Mar 28 '17 at 21:59
1

@psrpsrpsr PS: If you need the float result then use explicit type conversion `select 3::float/2;` or float notation `select 3/2.0;`. Good luck. – Abelisto Mar 30 '17 at 09:39

Clodoaldo Neto · Answer 2 · 2017-03-28T16:35:06.827

1

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

edited Mar 28 '17 at 16:35

answered Mar 28 '17 at 15:50

Clodoaldo Neto

118,695
26
233
260

I'm getting the following error: ERROR [42S02] ERROR: Function 'GENERATE_SERIES(INT4, INT4)' does not exist. Unable to identify a function that satisfies the given argument types. You may need to add explicit typecasts – psrpsrpsr Mar 28 '17 at 20:01
I believe I'm using it correctly - I'm using Aginity Workbench for Netezza. Is the GENERATE_SERIES() function only available via a plug in or something like that? – psrpsrpsr Mar 29 '17 at 13:11
@psrpsrpsr I don't know Netezza. According to the error message you are passing two integers as arguments to generate_series while my code passes dates and interval. – Clodoaldo Neto Mar 29 '17 at 13:21
Netezza is probably too old PostgreSQL fork for generate_series. – Pavel Stehule Mar 29 '17 at 18:41

score 1 · Answer 3 · answered Mar 28 '17 at 15:55

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)

score 0 · Answer 4 · answered Mar 28 '17 at 16:30

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;

light souls · Answer 5 · 2017-03-28T17:01:14.740

0

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

edited Mar 28 '17 at 17:01

answered Mar 28 '17 at 16:33

light souls

698
1
8
17

Any thoughts why I would be receiving this error message? (ERROR [HY000] ERROR: Cannot cast type 'INT4' to 'TEXT') – psrpsrpsr Mar 28 '17 at 20:02
I would suggest to put your whole query into to the question. I don't know actually how did you use my answer in your query? – light souls Mar 28 '17 at 20:25
Sorry I'm not able to find the backticks characters to format this as code: WITH t AS ( SELECT 201608 as d1, 201702 as d2 ) select t.d1, t.d2, trunc(EXTRACT(EPOCH from age(to_timestamp(t.d2::TEXT, 'YYYYMM'),to_timestamp(t.d1::TEXT, 'YYYYMM')))/(3600*24*30)), trunc(EXTRACT(EPOCH from age(to_timestamp(t.d2::TEXT, 'YYYYMM'),to_timestamp(t.d1::TEXT, 'YYYYMM')))/(3600*24*30)) +1 from t – psrpsrpsr Mar 29 '17 at 12:58
i dont know about netezza but according to error code, you need to find proper cast operator from int4 to text. maybe you should use `string` operator from your workbench btw your query is working – light souls Mar 29 '17 at 13:41

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

5 Answers5