CODEWITHSUNDEEP
mysqlsql
Don Rhummy
Don Rhummy

Reputation: 25830

How get TIMEDIFF of each row's date field with the closest previous date row without a subquery?

I need to calculate the TIMEDIFF between a row and the row whose field dateCompleted is the last one just before this one and then get the value as timeSinceLast.

I can do this easily as a subquery but it's very slow. (About 12-15 times slower than a straight query on the table for just the rows).

#Very slow
Select a.*, TIMDIFF(a.dateCompleted, (SELECT a2.dateCompleted FROM action a2 WHERE a2.dateCompleted < a.dateCompleted ORDER BY a2.dateCompleted DESC LIMIT 1)) as timeSinceLast
FROM action a;

I tried doing it as a join with itself but couldn't figure out how to get that work as I don't know how to do a LIMIT 1 on the join table and not the query as a whole.

#How limit the join table only?
SELECT a.*, TIMEDIFF(a.dateCompleted, a2.dateCompleted)
FROM action a
LEFT JOIN action a2 on a2.dateCompleted < a.dateCompleted 
LIMIT 1;

Is this possible in MySQL?

EDIT: Schema and data

http://sqlfiddle.com/#!9/03b5c/3

create table Actions
( 
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   dateCompleted datetime not null
);


#Notice, they can come out of order.
#   The third one would affect the first one in my query as
#   it's the first completed date right after the first

insert into Actions (dateCompleted)
values ("2016-05-06 12:11:01");

insert into Actions (dateCompleted)
values ("2016-05-06 12:11:03");

insert into Actions (dateCompleted)
values ("2016-05-06 12:11:02");

insert into Actions (dateCompleted)
values ("2016-05-06 12:11:05");

insert into Actions (dateCompleted)
values ("2016-05-06 12:11:04");

Result (order by dateCompleted):

id  dateCompleted          timeSinceLast
 1, "2016-05-06 12:11:01", null
 3, "2016-05-06 12:11:02", 1
 2, "2016-05-06 12:11:03", 1
 5, "2016-05-06 12:11:04", 1
 4, "2016-05-06 12:11:05", 1

(In this simple example, they all had a one second time since the next one)

Upvotes: 0

Views: 58

Answers (1)

Strawberry
Strawberry

Reputation: 33945

SELECT x.*
     , MIN(TIMEDIFF(x.datecompleted,y.datecompleted)) 
  FROM actions x 
  LEFT 
  JOIN actions y 
    ON y.datecompleted < x.datecompleted 
 GROUP 
    BY x.id 
 ORDER 
    BY x.datecompleted;

...or faster...

SELECT x.*
     , TIMEDIFF(datecompleted,@prev)
     , @prev:=datecompleted 
  FROM actions x
     , (SELECT @prev:=null) vars 
 ORDER 
    BY datecompleted;

Upvotes: 2

Related Questions