0

I have a retention query for monthly users where i wrote this which is similar to the one Weekly Cohort analysis Mysql

The query works fine and outputs correct, i just need to change only the date format in the column

The query works with two table

deviceinfo and session table,

deviceinfo table has id(id), device_id(unique deviceid) and created_at(datetime)

session table has id(id of session table), deviceid(deviceid which refers deviceinfo id) and start_time(datetime)

 SELECT STR_TO_DATE(CONCAT(tb.cohort), '%Y-%m-%d') as date,
 m1,
 m2,
 m3,
 m4
 FROM
 (SELECT u.cohort, IFNULL(SUM(s.Offset = 0), 0) m1,
 IFNULL(SUM(s.Offset = 1), 0) m2,
 IFNULL(SUM(s.Offset = 2), 0) m3,
 IFNULL(SUM(s.Offset = 3), 0) m4
 FROM
 (SELECT id, DATE_FORMAT(created_at, '%Y-%m') AS cohort FROM deviceinfo 
  WHERE created_at BETWEEN '2017-09-01' AND '2017-11-30' ) as u
  LEFT JOIN
 (SELECT DISTINCT sessions.deviceid, FLOOR(DATEDIFF(sessions.start_time, 
  deviceinfo.created_at)/30) AS Offset
  FROM sessions
  LEFT JOIN deviceinfo ON (deviceinfo.id = sessions.deviceid)WHERE 
  sessions.start_time BETWEEN '2017-09-01' AND '2017-11-30' ) as s
  ON s.deviceid = u.id GROUP BY u.cohort ) as tb
  LEFT JOIN ( SELECT DATE_FORMAT(created_at, '%Y-%m-%d') dt,
  COUNT(*) size FROM deviceinfo GROUP BY dt ) size ON tb.cohort = size.dt

And my result to the query is 

date               m1    m2    m3  

2017-09-00         6      3     0     

2017-09-00         6      0     0    

2017-09-00         32     0     0

But my expected result is

date            m1    m2    m3  

2017-09-01      6      3    0     

2017-09-01      6      0    0    

2017-09-01      32     0    0

I know this is basic thing in Mysql since i am new to it am struck with this any help will be useful Thank you in advance

White Rose
  • 11
  • 7

1 Answers1

0

You can make use of the ADDDATE mysql function to achieve this.

example code

mysql> SELECT ADDDATE('2017-09-00', 1);
      -> '2017-09-01'

Refer Documentation here for more information on the function.

RamC
  • 1,287
  • 1
  • 11
  • 15