CODEWITHSUNDEEP
mysqltime
Fid
Fid

Reputation: 506

Calculating time difference in MySQL but taking events into account

I have a table structured like this

index - date ------ time ----- status
1       2015-01-01  13:00:00   start
2       2015-01-01  13:10:00   continue
3       2015-01-01  13:20:00   continue
4       2015-01-01  13:30:00   end
5       2015-01-01  13:30:00   ready
6       2015-01-01  13:40:00   start
7       2015-01-01  13:50:00   continue
8       2015-01-01  15:00:00   end

and what I would like to do is count the time between start and end (ie. index 1-4 is 30min, 6-8 is 20min), but taking into account only the first start and first end, so that the query doesn't choose the time difference of index 1-8. Which query is used to calculate time difference between two statues (start-end) and show the result for multiple instances of start-end without them getting batched into one event?

Upvotes: 1

Views: 1643

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dt DATETIME NOT NULL
,status VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1       ,'2015-01-01 13:00:00' ,  'start'),
(2       ,'2015-01-01 13:10:00' ,  'continue'),
(3       ,'2015-01-01 13:20:00' ,  'continue'),
(4       ,'2015-01-01 13:30:00' ,  'end'),
(5       ,'2015-01-01 13:30:00' ,  'ready'),
(6       ,'2015-01-01 13:40:00' ,  'start'),
(7       ,'2015-01-01 13:50:00' ,  'continue'),
(8       ,'2015-01-01 15:00:00' ,  'end');

SELECT x.*
     , TIMEDIFF(MIN(y.dt),x.dt)diff 
  FROM my_table x 
  JOIN my_table y 
    ON y.dt >= x.dt 
 WHERE x.status = 'start' 
   AND y.status = 'end' 
 GROUP 
    BY x.id;
+----+---------------------+--------+----------+
| id | dt                  | status | diff     |
+----+---------------------+--------+----------+
|  1 | 2015-01-01 13:00:00 | start  | 00:30:00 |
|  6 | 2015-01-01 13:40:00 | start  | 01:20:00 |
+----+---------------------+--------+----------+

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270553

For each start, use a query to get the next end time. Then, just calculate the difference. The logic is something like this:

select t.*, timestampdiff(second, dt, next_dt)
from (select t.*, addtime(t.date, t.time) as dt,
             (select addtime(t2.date, t2.time)
              from table t2
              where addtime(t2.date, t2.time) > addtime(t.date, t.time) and
                    status = 'end'
              order by addtime(t2.date, t2.time) desc
              limit 1
             ) as next_dt
      from table t
      where status = 'start'
     ) t

This assumes that your date and time columns are stored using the proper database types (date and time). If you are storing them as something else, you will have to needlessly complicate the logic to convert them to the internal formats.

Upvotes: 2

Related Questions