How to check the number of consecutive days in a mysql date column

Question

There is a MySQL table which lists health workouts.

recordId (primary key, ,integer, auto incrementing)
workoutNumber (integer)
date (date, ex- "2014-07-29")

I need to know how many last consecutive days the user has been working out. Can we do this in a MySQL Query ? I am using PHP as the application language.

It depends on how you're storing the date, but no, you can't do this just with a query. What you can do is query the user's record of workouts ordered by date, then do a quick js to scan the dates until you find a discontinuity. — JTravakh, Jul 28 '14 at 23:06
@JTravakh it can be done in MySQL (and in other RDBMS), by using a little trick with temp variables (see answer below) — Barranka, Jul 28 '14 at 23:11
you can do it without variables as well, but variables are cool too — Strawberry, Jul 28 '14 at 23:36

score 1 · Answer 1 · answered Jul 28 '14 at 23:10

I can give you a "pure SQL" solution, using temp variables:

This query will create a column with 1s for consecutive days, and 0s if the days are not consecutive.

select a.*
    , coalesce(date_diff(a.date, @prevDate), 0) = 1 as consecutiveDay -- '1' if the days are consecutive,
                                                                      -- '0' otherwise
                                                                      -- (The first row will be 0)
    , @prevDate := a.date as this_date -- You need to store the date of the current record
                                       -- to compare it with the next one
from
    (select @prevDate := null) as init  -- This is where you initialize the temp 
                                        -- variable that will track the previous date
    , yourTable as a
-- WHERE -- (Put any where conditions here)
order by a.date;

Now you can sum the ones using the above query as a row source for a second query:

select sum(consecutiveDays) as consecutiveDays
from 
    ( select a.*
           , coalesce(date_diff(a.date, @prevDate), 0) = 1 as consecutiveDay
           , @prevDate := a.date as this_date
      from (select @prevDate := null) as init
         , yourTable as a
      -- WHERE -- (add where conditions here)
      order by a.date
    ) as b

Hope this helps

pid · Answer 2 · 2014-07-28T23:16:25.950

You can do it this way.

Combine the previous day with the current day and yesterday with the day before. This join would look like this:

SELECT *
FROM table AS t1 INNER JOIN table AS t2 ON t1.date = SUBDATE(t2.date, 1) --or whatever you need to get the previous day

You will now notice that unpaired days are on the boundaries, while paired days are on the inside of consecutive intervals. To obtain the boundaries you have to use an outer join.

SELECT *
FROM table AS t1 FULL OUTER JOIN table AS t2 ON t1.date = SUBDATE(t2.date, 1)

Now what you need is to select just those unpaired dates:

SELECT *
FROM table AS t1 FULL OUTER JOIN table AS t2 ON t1.date = SUBDATE(t2.date, 1)
WHERE t1.date IS NULL OR t2.date IS NULL;

When t1.date IS NULL it is a starting day. When t2.date IS NULL it is an ending day.

Just take the MAX() of what you need:

SELECT MAX(t2.date)
FROM table AS t1 FULL OUTER JOIN table AS t2 ON t1.date = SUBDATE(t2.date, 1)
WHERE t1.date IS NULL;

DISCLAIMER I jotted down this answer on the fly, the -1, IS NULL or MIN() vs MAX() may be wrong but the basic idea should be clear.

As an explanation, what you want is this:

t1          t2
NULL        16/07/1994  <-- interesting join (start of an interval)
16/07/1994  17/07/1994
17/07/1994  18/07/1994
18/07/1994  NULL        <-- interesting join (end of an interval)

If you just need the start or the end you can use a `LEFT JOIN` or a `RIGHT JOIN`. The `FULL OUTER JOIN` is just for illustration or when you need both. And you can still [work around this limitation](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql). — pid, Jul 28 '14 at 23:25

How to check the number of consecutive days in a mysql date column

2 Answers2