Get 3 consecutive days from MySQL data

Question

I am trying to use a query from this SO question Check for x consecutive days - given timestamps in database to count a number of consecutive days a user has submitted an activity i.e. 3 days, 5 days, 7 days etc.

The query is:

SELECT IF(COUNT(1) > 0, 1, 0) AS has_consec
FROM
(
SELECT *
FROM
(
    SELECT IF(b.dateAdded IS NULL, @val:=@val+1, @val) AS consec_set
    FROM activity a
    CROSS JOIN (SELECT @val:=0) var_init
    LEFT JOIN activity b ON 
        a.userID = b.userID AND
        a.dateAdded = b.dateAdded + INTERVAL 1 DAY
    WHERE a.userID = 1
) a
GROUP BY a.consec_set
HAVING COUNT(1) >= 3
) a

The code works great when the date field is not dateTime but how would I modify the code to ignore the time component of dateTime? I have tried using DATE(dateAdded) but that didn't work.

My data looks like:

userID  dateAdded
1       2016-07-01 17:01:56
1       2016-07-02 12:45:49
1       2016-07-03 13:06:27
1       2016-07-04 12:51:10
1       2016-07-05 15:51:10
2       2016-07-06 16:51:10
2       2016-07-07 11:51:10
1       2016-07-08 11:26:38

Thanks

Try this http://www.tutorialspoint.com/mysql/mysql-date-time-functions.htm#function_timestampadd — Piyush Gupta, Jul 12 '16 at 04:05
Try casting your date time field to date like this `DATE(a.dateAdded)` — 1000111, Jul 12 '16 at 04:41
Tried that and it didn't work. If I change the time component to be the same the query works but even casting to DATE didn't produce the correct results. — puks1978, Jul 12 '16 at 04:42

score 0 · Answer 1 · answered Jul 12 '16 at 05:29

Casted the dateAdded field to Date.

Please give it a try and let me know if it resolves the issue:

SELECT IF(COUNT(1) > 0, 1, 0) AS has_consec
FROM
(
SELECT *
FROM
(
    SELECT IF(b.dateAdded IS NULL, @val:=@val+1, @val) AS consec_set
    FROM activity a
    CROSS JOIN (SELECT @val:=0) var_init
    LEFT JOIN activity b ON 
        a.userID = b.userID AND
        DATE(a.dateAdded) = DATE(b.dateAdded) + INTERVAL 1 DAY
    WHERE a.userID = 1
) a
GROUP BY a.consec_set
HAVING COUNT(1) >= 3
) a;

Note: Using timestamp will return correct output only if they are having same time (hh:mm:ss)

Get 3 consecutive days from MySQL data

1 Answers1