Postgresql weird outcome after joining on dates with interval of 1 month

I have a table like this:

     Date  user_id
========= =======
 1/2/2018    1
 3/4/2018    2
 2/2/2018    1
4/17/2018    2
etc.

The table indicates the date a user accessed a website and user_id is the user id.

I wanted to check whether the user logged in a month and in the previous month. I wrote the next query:

SELECT a.user_id, to_char(a.date::date, 'YYYY-MM') as a, to_char(b.date::date, 'YYYY-MM') as b
FROM mytable a
         left JOIN mytable b
         on to_char(a.date::date, 'YYYY-MM') = to_char(b.date::date - INTERVAL '1 month', 'YYYY-MM')
         and a.user_id = b.user_id
GROUP BY 1,2,3
ORDER BY 1

However, when I run this query table a shows the previous month and table b is the next month like:

   a.Date    b.Date  user_id
========= ========== ======
 1/2/2018  2/2/2018   1
 3/4/2018  4/17/2018  2

I expected this to be the other way around, why is the output like this? I guess it can be also done with partition?

Edit: By the other way around what I meant is that the output I got a.Date should have the data in b.Date and otherwise.

Edit 2: Solved and shared my solution.

Answer

I had to join this way:

on to_char(a.date::date , 'YYYY-MM') = to_char(b.date::date+ INTERVAL '1 month', 'YYYY-MM')

Which mean the current month for example is 10 and the previous is 9 +1 and then it will print the desired result.

Advertisement

Answer