SQL statement for calculating the time difference between two rows formed by different conditions in the same column

SQL statement table for calculating the time difference between two rows formed by different conditions in the same column

CREATE TABLE `hp_report` (  `id` int(10) unsigned NOT NULL auto_increment,`code` varchar(255) NOT NULL,  `content` mediumtext NOT NULL,  `ctime` datetime NOT NULL,) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=662555 ;INSERT INTO `hp_report` VALUES (2,  'a', 'on',  '2014-07-04 21:17:53');INSERT INTO `hp_report` VALUES (3,  'a', 'abc',  '2014-07-04 21:18:53');INSERT INTO `hp_report` VALUES (4,  'a', 'off',  '2014-07-04 21:19:53');INSERT INTO `hp_report` VALUES (5,  'b', 'on',  '2014-07-04 21:20:53');INSERT INTO `hp_report` VALUES (6,  'b', 'abc',  '2014-07-04 21:22:53');INSERT INTO `hp_report` VALUES (7,  'b', 'off',  '2014-07-04 21:29:53');INSERT INTO `hp_report` VALUES (8,  'a', 'on',  '2014-07-04 21:34:53');INSERT INTO `hp_report` VALUES (9,  'a', 'abc',  '2014-07-04 21:36:53');INSERT INTO `hp_report` VALUES (10,  'a', 'off',  '2014-07-04 21:45:53');INSERT INTO `hp_report` VALUES (11,  'b', 'on',  '2014-07-04 22:12:53');INSERT INTO `hp_report` VALUES (13,  'b', 'abc',  '2014-07-04 22:18:53');INSERT INTO `hp_report` VALUES (14,  'b', 'off',  '2014-07-04 22:19:53');

I want to find the ctime difference between a and B, and the content is off, and sum the difference
That is:

Insert into 'HP _ report' VALUES (4, 'A', 'off', '2017-07-04 21:19:53 '); and insert into 'HP _ report' VALUES (2, 'A', 'on', '2017-07-04 21:17:53 '); difference between ctime (2 minutes) insert into 'HP _ report' VALUES (8, 'A', 'on', '2017-07-04 21:34:53 '); insert into 'HP _ report' VALUES (10, 'A', 'off', '2017-07-04 21:45:53 '); this is 11 minutes. And add up all the difference values of. The result I want is a 13b 16.

Please help me. Thank you !!

Reply to discussion (solution)

Can I calculate the time after obtaining it?
Strtotime ($ row ['ctime']);

Echo (strtotime ('2017-07-04 21:19:53 ')-strtotime ('2017-07-04 21:17:53 '));
The result is 120 seconds.

mysql> select * from hp_report;+----+------+---------+---------------------+| id | code | content | ctime               |+----+------+---------+---------------------+|  2 | a    | on      | 2014-07-04 21:17:53 ||  3 | a    | abc     | 2014-07-04 21:18:53 ||  4 | a    | off     | 2014-07-04 21:19:53 ||  5 | b    | on      | 2014-07-04 21:20:53 ||  6 | b    | abc     | 2014-07-04 21:22:53 ||  7 | b    | off     | 2014-07-04 21:29:53 ||  8 | a    | on      | 2014-07-04 21:34:53 ||  9 | a    | abc     | 2014-07-04 21:36:53 || 10 | a    | off     | 2014-07-04 21:45:53 || 11 | b    | on      | 2014-07-04 22:12:53 || 13 | b    | abc     | 2014-07-04 22:18:53 || 14 | b    | off     | 2014-07-04 22:19:53 |+----+------+---------+---------------------+12 rows in set (0.00 sec)mysql> select `code`,sum(k) from (    -> select `code`,    ->  TIMESTAMPDIFF(MINUTE,(select max(ctime) from hp_report where `code`=a.code and ctime from hp_report a    -> where content='off'    -> ) t    -> group by `code`;+------+--------+| code | sum(k) |+------+--------+| a    |     10 || b    |      8 |+------+--------+2 rows in set (0.00 sec)mysql>

select code,  time_format(    sum(timediff(btime, atime)  ), '%i') as xtime  from     (      select code,        ctime as atime,        (select min(ctime) from hp_report where id>t.id and content='off') as btime        from hp_report t        where content='on'    ) A  group by code

code xtime a    13 b    16 

In fact, your storage solution is very dangerous:
If one on or off is lost for some reason, the entire data relationship will be messy.
Therefore, if there is a ing between lines, you should add a field to explicitly indicate this relationship.
Or split the table into two tables according to the relational database paradigm.
For your data, you can put on time and off time in a record.

A good data structure can get twice the result with half the effort
On the contrary, nothing can be said, but it will be exposed.

Thank you for your reply.
Thank you very much for the xuzuning on the third floor. The result is correct. However, my table records millions of records. So it runs slowly for about 2 minutes. I wonder if there is any way to improve efficiency.

Table design problems. Since there will be other information in the future, I don't know when to turn off. Therefore, if a record is stored, the intermediate information cannot be stored.
In addition, does the Split Table mean to reduce the number of records for each table or for other reasons. Please advise. thank you !!

Could you recommend one or two books on highly efficient SQL statements? thank you very much !!