aggregate functions - I want to to find a way to get my appropriate result in 1 mysql query

Question

I have a table name order_history where I store both old_status and new_status of company orders. the schema of table :

CREATE TABLE order_history (
  id int(11) NOT NULL AUTO_INCREMENT,
  old_status longtext COLLATE utf8_unicode_ci,
  new_status longtext COLLATE utf8_unicode_ci,
  created_at datetime NOT NULL,
  order_id int(11) DEFAULT NULL,
  PRIMARY KEY (id)
 }

The insert to populate is :

INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (1, '56', '714', '2020-12-20 21:37:54', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (2, '714', '61', '2020-12-20 21:37:56', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (3, '61', '713', '2020-12-20 21:38:17', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (4, '713', '42', '2020-12-20 21:38:26', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (5, '42', '51', '2020-12-20 21:59:17', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (6, '56', '714', '2020-12-20 22:21:27', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (7, '714', '61', '2020-12-20 22:21:29', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (8, '61', '713', '2020-12-20 22:24:28', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (9, '713', '42', '2020-12-20 22:24:43', 94471496);

And Now the question I want to find the TIMEDIFF of created_ats between rows that new_status=61 and rows that new_status=42 and old_status=713.

So in the example the affected rows should be (2,4,7,9) , and the right answer will be the TIMEDIFF between rows with ids (2,4) and rows with ids (7,9). But my query returns 3 results instead of 2 and it also calculate the TIMEDIFF between rows (2,9). How can I exclude this result? Here is my query:

select *
from (select oschStart.order_id as order_id, TIMEDIFF(oschEnd.created_at, oschStart.created_at) as confirm_time
      from (select osch1.order_id, osch1.created_at
            from order_history osch1
            where osch1.old_status = 713
              and osch1.new_status = 42
           ) oschEnd
               join (select osch1.order_id, osch1.created_at
                     from order_history osch1
                     where osch1.new_status = 61
      ) oschStart
                    on oschStart.order_id = oschEnd.order_id and oschEnd.created_at > oschStart.created_at) order_time;

Answer 1

A simpler approach is to use a correlated sub query

select *,
        timediff(
        (select created_at from order_history oh1 
            where oh1.order_id = oh.order_id and
                  oh1.id > oh.id and
                  oh1.old_status = '713' and oh1.new_status = '42'
         order by oh1.id asc limit 1),oh.created_at) diff
from order_history oh
where  new_status = 61;