Find period since last pause in MySQL

Question

I need to find the period without pause in days since last pause. I have a next table:

id | user |    date
-----------------------
 1 |  1   | 16.02.2017   
 1 |  1   | 15.02.2017
 1 |  1   | 14.02.2017
 1 |  1   | 13.02.2017
 1 |  1   | 10.02.2017

Last pause: 10-13 February

Last period without pause: 4 days

I tried to found the difference between day like in this question, but as result it was always NULL. And this is only first part. For second part I thought to use something like ranking, but don't know if it will work.

I plan to use it with PHP 7 + MySQL 5.6.

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 16 '17 at 09:33
just found this [link](http://stackoverflow.com/questions/25272098/mysql-count-consecutive-dates-for-current-streak) hopefully it will work — Lev, Feb 16 '17 at 09:41

score 2 · Accepted Answer · answered Feb 16 '17 at 09:54

I've used this sample:

create table if not exists myt(id int, dd date);
insert into myt values
(1, '2017-01-01'),
(1, '2017-01-02'),
(1, '2017-01-03'),
(1, '2017-01-04'),
(1, '2017-01-08'),
(1, '2017-01-09'),
(1, '2017-01-10');

First you should set a partition by consecutive days:

select id, dd, 
       if(@last_date = '1900-01-01' or datediff(dd, @last_date) = -1, @cn := @cn, @cn := +1) consecutive, 
       @last_date := dd
from
    (select @last_date := '1900-01-01', @cn := 0) x,
    (select id, dd
     from myt
     order by dd desc) y
;

This returns:

+----+---------------------+-------------+
| id | dd                  | consecutive |
+----+---------------------+-------------+
| 1  | 10.01.2017 00:00:00 |      0      |
| 1  | 09.01.2017 00:00:00 |      0      |
| 1  | 08.01.2017 00:00:00 |      0      |
+----+---------------------+-------------+
| 1  | 04.01.2017 00:00:00 |      1      |
| 1  | 03.01.2017 00:00:00 |      1      |
| 1  | 02.01.2017 00:00:00 |      1      |
| 1  | 01.01.2017 00:00:00 |      1      |
+----+---------------------+-------------+

After you set a partition, then get MAX and MIN date for each partition:

select id, min(dd) as ini, max(dd) as fin, datediff(max(dd), min(dd)) as Days
from (
        select id, dd, 
               if(@last_date = '1900-01-01' or datediff(dd, @last_date) = -1, @cn := @cn, @cn := +1) consecutive, 
               @last_date := dd
        from
            (select @last_date := '1900-01-01', @cn := 0) x,
            (select id, dd
             from myt
             order by dd desc) y
    ) z
group by consecutive
;

Result:

+----+---------------------+---------------------+------+
| id |         ini         |         fin         | Days |
+----+---------------------+---------------------+------+
| 1  | 08.01.2017 00:00:00 | 10.01.2017 00:00:00 |   2  |
+----+---------------------+---------------------+------+
| 1  | 01.01.2017 00:00:00 | 04.01.2017 00:00:00 |   3  |
+----+---------------------+---------------------+------+

Check it: http://rextester.com/XMIX80360

Devart · Answer 2 · 2017-02-16T09:49:11.703

1

Try this query. It will find all pauses -

SELECT curr_date, prev_date FROM (
  SELECT t1.date curr_date, MAX(t2.date) prev_date FROM periods t1
    LEFT JOIN periods t2
      ON t1.date > t2.date
    GROUP BY t1.date) t
WHERE DATEDIFF(curr_date, prev_date) > 1

The result is:

13-Feb-17   10-Feb-17

Then add condition/LIMIT to get only one row.

edited Feb 16 '17 at 09:49

answered Feb 16 '17 at 09:43

Devart

119,203
23
166
186

Works, but doesn't show if user is now in one of pauses. – Lev Feb 17 '17 at 07:21

score 0 · Answer 3 · answered Feb 16 '17 at 09:42

I am trying to write general query like,

select count(distinct t1.`date`) from period t1 left join period t2 ON t2.user = t1.user and t1.`date` - INTERVAL 1 DAY = t2.date where t2.id is null

Can you try this query once, it should work, I have used it in my almost same case.

score 0 · Answer 4 · answered Feb 16 '17 at 09:56

This query will return the latest hole:

select
  m.`date`,
  min(m1.`date`) as next_date,
  datediff(min(m1.`date`), m.`date`)+1 as diff
from
  mytable m left join mytable m1
  on m.`date`<m1.`date`
group by
  m.`date`
having
  datediff(min(m1.`date`), m.`date`)>1
order by
  m.`date` desc
limit 1

score 0 · Answer 5 · answered Feb 16 '17 at 09:57

The following query:

SELECT MIN(`date`) AS date_start,
       MAX(`date`) AS date_end,
       MAX(days_diff) AS pause_days,
       COUNT(*) + 1 AS period_without_pays
FROM (
   SELECT id, user, `date`, 
          DATE_SUB(`date`, INTERVAL rn DAY) AS group_date,
          DATEDIFF(`date`, COALESCE(prevDate, `date`)) AS days_diff
   FROM (       
      SELECT t1.id, t1.user, t1.`date`, 
             @rn := @rn + 1 AS rn,
             (SELECT t2.`date`
              FROM mytable AS t2        
              WHERE t1.id = t2.id AND t1.user = t2.user AND t1.`date` > t2.`date`
              ORDER BY `date` DESC LIMIT 1) AS prevDate
      FROM mytable AS t1
      CROSS JOIN (SELECT @rn := 0) AS v
      ORDER BY `date`) AS t) AS x
GROUP BY id, user, group_date, days_diff
HAVING SUM(days_diff) > 0

returns:

date_start date_end   pause_days  period_without_pays
-----------------------------------------------------
2017-02-14 2017-02-16 1           4
2017-02-13 2017-02-13 3           2

The row with pause_days > 1 returns the start date of the pause along with the number of days.
The row with pause_days = 1 returns the start / end dates of an island of consecutive records having consecutive dates along with the count of these dates.

Note: The above query works with the sample data provided. You may have to tweak the query a little bit so as to adjust it to the complexity of the actual data.

krishna aryal · Answer 6 · 2017-02-17T04:42:33.107

Try this one:

SET @dateDiff=NULL;SET @dateDiff2='';
SELECT diff.secondDate AS fromDate,diff.initialDate AS toDate, diff.dateDiffR FROM (
SELECT d.date AS initialDate,@dateDiff AS secondDate,IF(@dateDiff IS NULL,@dateDiff:=d.date,0) AS try,
IF(DATE(@dateDiff)!=DATE(d.date),DATEDIFF(d.date,@dateDiff),NULL) AS dateDiffR,
IF(@dateDiff!=@dateDiff2,@dateDiff2:=@dateDiff,0) AS try1,
IF(DATE(@dateDiff)!=DATE(d.date),@dateDiff:=d.date,NULL) AS assign FROM
(SELECT b.date FROM mytable b)d ) diff WHERE diff.dateDiffR>0

it will give you the date difference with its date range. If you get negative count then swap the dates on parameters' for DATEDIFF;

score 0 · Answer 7 · answered Feb 21 '17 at 20:48

(Posted on behalf of the OP).

I adapted little bit query from @McNets:

select user, min(dd) as ini, max(dd) as fin, datediff(max(dd), min(dd))+1 as Days, consecutive
from (
    select user,dd, 
           if(@last_date = curdate() or datediff(dd, @last_date) >= -1, @cn := @cn, @cn := @cn+1) consecutive,
           @last_date := dd
    from
        (select @last_date := curdate(), @cn := 0) x,
        (select user, date as dd
         from myt
         where user = %id
         order by dd desc) y
 ) z
group by consecutive 
order by CAST(consecutive AS UNSIGNED)

I added filter by user, changed cause to '>= -1' to accept time usage, added number of the serie and changed initial date from '1900-01-01' to CURDATE() function (i don't see any influence to query result from this action).

Now using number of the series can find the longest series and its dates.

Find period since last pause in MySQL

7 Answers7