How to display continuous date in mysql, If date is not present in table

Question

This is a retention query i need to fetch continuous data from db i have written this query with two tables

1. deviceinfodatas - has the id , created_at time

2. sessions - deviceid, start_time

   SELECT

   u.DayOffset as Date,

   SUM(s.DayOffset = 0) AS d0,

   SUM(s.DayOffset = 1) AS d1,

   SUM(s.DayOffset = 2) AS d2,

   SUM(s.DayOffset = 3) AS d3

   FROM

   ( SELECT id, DATE(created_at) AS DayOffset FROM deviceinfodatas WHERE created_at BETWEEN '2017-10-01' AND '2017-12-30') as u LEFT JOIN

   ( SELECT DISTINCT sessions.deviceid, DATEDIFF(DATE(sessions.start_time), DATE(deviceinfodatas.created_at)) AS DayOffset FROM sessions

   LEFT JOIN deviceinfodatas ON (deviceinfodatas.id = sessions.deviceid) WHERE sessions.start_time BETWEEN '2017-10-01' AND '2017-12-30' ) as s ON s.deviceid = u.id GROUP BY u.DayOffset

My result is

Date          d0  d1  d2  d3

2017-10-25    1   0   0   0

2017-10-27    1   0   0   0

2017-10-31    0   0   1   1

2017-11-02    1   0   0   0

2017-11-03    1   0   0   0

2017-11-06    1   0   0   0

2017-11-08    1   0   0   0

2017-11-09    0   0   1   0

2017-11-13    0   0   1   0

2017-11-16    5   1   0   0

But i need a continuous date

as

 Date          d0  d1  d2  d3

2017-10-25    1   0   0   0

2017-10-26    0   0   0   0

2017-10-27    1   0   0   0

2017-10-28    0   0   0   0

2017-10-29    0   0   0   0

2017-10-30    0   0   0   0

2017-10-31    0   0   1   1

.....

2017-11-16    5   1   0   0

And also based on date range the count of day d0,d1,d2,d3,d4,d5.... dn has to increased dynamically

I am a beginner in Mysql please do help with this query to achieve my result

i has referenced Continuous date and Daily retention query

Thank you in advance

Answer 1

If you want the date values returned, your query will need a row source for the date values. That could be a calendar table cal populated with all of the date values you need to return, or it could be an inline view that dynamically returns the set of dates.

Example of a simple calendar table containing unique values. This table will need to be maintained, we need to make sure it contains all possible date values that we would want to return, with no gaps.

CREATE TABLE cal (dt DATE NOT NULL PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO cal (dt) VALUES ('2017-10-01'),('2017-10-02'),('2017-10-03') ;
INSERT INTO cal (dt) SELECT dt + INTERVAL 3 DAY FROM cal ORDER BY dt ;
INSERT INTO cal (dt) SELECT dt + INTERVAL 6 DAY FROM cal ORDER BY dt ;
INSERT INTO cal (dt) SELECT dt + INTERVAL 12 DAY FROM cal ORDER BY dt ;
INSERT INTO cal (dt) SELECT dt + INTERVAL 24 DAY FROM cal ORDER BY dt ;
INSERT INTO cal (dt) SELECT dt + INTERVAL 48 DAY FROM cal ORDER BY dt ; 
INSERT INTO cal (dt) SELECT dt + INTERVAL 96 DAY FROM cal ORDER BY dt ;

Then we can do something like this:

SELECT c.dt 
     , ...
  FROM cal c

  LEFT
  JOIN mytable t
    ON t.date_col = c.dt 

WHERE c.dt >= '2017-10-25'
  AND c.dt <  '2017-10-25' + INTERVAL 30 DAY

Answer 2

Here is the working query

Without creating a separate table it is easy to get continuous date

This may help someone !

SELECT u.DayOffset as Date,
COALESCE(SUM(s.DayOffset = 0),0) AS d0,
COALESCE(SUM(s.DayOffset = 1),0) AS d1,
COALESCE(SUM(s.DayOffset = 2),0) AS d2,
COALESCE(SUM(s.DayOffset = 3),0) AS d3,
COALESCE(SUM(s.DayOffset = 4),0) AS d4,
COALESCE(SUM(s.DayOffset = 5),0) AS d5,
COALESCE(SUM(s.DayOffset = 6),0) AS d6,
COALESCE(SUM(s.DayOffset = 7),0) AS d7,
COALESCE(SUM(s.DayOffset = 8),0) AS d8,
COALESCE(SUM(s.DayOffset = 9),0) AS d9
FROM ( SELECT id, DATE(d.created_at) AS DayOffset from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + 
t0.i) `created_at` from
(select 0 i union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) 
t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 
union 
select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) 
t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) 
t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 
union select 5 union select 6 union select 7 union select 8 union select 9) 
t4) d
left join deviceinfodatas v on DATE(d.`created_at`) = DATE(v.`created_at`)
where DATE(d.`created_at`) between '2017-11-01' and '2017-11-11') as u
LEFT JOIN ( SELECT DISTINCT sessions.deviceid, 
DATEDIFF(DATE(sessions.start_time), DATE(deviceinfodatas.created_at))
AS DayOffset FROM sessions
LEFT JOIN deviceinfodatas ON (deviceinfodatas.id = sessions.deviceid) WHERE 
sessions.start_time BETWEEN '2017-11-01' AND '2017-11-11' )
as s ON s.deviceid = u.id GROUP BY u.DayOffset 

Here is the result

Date        d0  d1  d2  d3  d4  d5  d6  d7  d8  d9
2017-11-01  0   0   0   0   0   0   0   0   0   0

2017-11-02  1   0   0   0   0   0   0   0   0   0

2017-11-03  1   0   0   0   0   1   0   0   1   0

2017-11-04  0   0   0   0   0   0   0   0   0   0

2017-11-05  0   0   0   0   0   0   0   0   0   0

2017-11-06  1   0   0   0   0   0   0   0   0   0

2017-11-07  0   0   0   0   0   0   0   0   0   0

2017-11-08  1   0   0   0   0   0   0   0   0   0

2017-11-09  0   0   1   0   0   0   0   0   0   0

2017-11-10  0   0   0   0   0   0   0   0   0   0

2017-11-11  0   0   0   0   0   0   0   0   0   0

2017-11-12  0   0   0   0   0   0   0   0   0   0

I can achieve continuous date but still working on getting day count d0,d1,.... d12

I am trying in php mysql where i got the date difference count in php trying to use in query , if any hints will be appreciated

Thank you