Finding time difference between 2 rows from a table

163
January 27, 2018, at 01:21 AM
LogRowID    Stamp                    Comment
55170   1/25/2018 11:04:29 PM   END uspArchiveBFAM run
55120   1/25/2018 5:30:20 PM    Begin BFAM Archive process
55119   1/24/2018 11:17:36 PM   END uspArchiveBFAM run
55069   1/24/2018 5:30:25 PM    Begin BFAM Archive process
55068   1/23/2018 10:59:48 PM   END uspArchiveBFAM run
55018   1/23/2018 5:30:07 PM    Begin BFAM Archive process
55017   1/19/2018 10:38:56 PM   END uspArchiveBFAM run
54967   1/19/2018 5:30:08 PM    Begin BFAM Archive process

LogRowID are increased by 50 for each process. (Begin to End)

I want to create a table with the date of the process and the duration of the process for that day. (2 columns)

WANT:

DATE | DURATION

1/25/2018 | 5:34:09
1/24/2018 | 5:47:11
1/23/2018 | 5:29:41

Edit: This is what i have so far:

select  distinct date_format(stamp, '%m/%d/%y') as 'Date',
(select TIMEDIFF((select stamp from bfam_archive_log where (date(stamp) = subdate(CURRENT_DATE,1) and comment ='END uspArchiveBFAM run')),
(select stamp from bfam_archive_log where (date(stamp) = subdate(CURRENT_DATE,1) and comment = 'Begin BFAM Archive Process')))) as 'Duration'
from bfam_archive_log

Current Code

I dont know how to calculate the difference for more than 1 pair of datetimes.

Answer 1

Lots of assumption here, but anyway...

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(LogRowID SERIAL PRIMARY KEY
,Stamp DATETIME 
,Comment VARCHAR(12) NOT NULL
);
INSERT INTO my_table VALUES
(55170,'2018-01-25 11:04:29' ,'END'),
(55120,'2018-01-25 05:30:20' ,'Begin'),
(55119,'2018-01-24 11:17:36' ,'END'),
(55069,'2018-01-24 05:30:25' ,'Begin'),
(55068,'2018-01-23 10:59:48' ,'END'),
(55018,'2018-01-23 05:30:07' ,'Begin'),
(55017,'2018-01-19 10:38:56' ,'END'),
(54967,'2018-01-19 05:30:08' ,'Begin');
SELECT x.*
     , TIMEDIFF(MIN(y.stamp),x.stamp) diff 
  FROM my_table x 
  JOIN my_table y ON DATE(y.stamp) = DATE(x.stamp) 
   AND y.stamp > x.stamp 
   AND y.comment = 'end' 
 WHERE x.comment = 'begin' 
 GROUP 
    BY x.logrowid;
+----------+---------------------+---------+----------+
| LogRowID | Stamp               | Comment | diff     |
+----------+---------------------+---------+----------+
|    54967 | 2018-01-19 05:30:08 | Begin   | 05:08:48 |
|    55018 | 2018-01-23 05:30:07 | Begin   | 05:29:41 |
|    55069 | 2018-01-24 05:30:25 | Begin   | 05:47:11 |
|    55120 | 2018-01-25 05:30:20 | Begin   | 05:34:09 |
+----------+---------------------+---------+----------+
READ ALSO
MySQL Get duration between gaps

MySQL Get duration between gaps

I'm trying to calculate the duration where a unit was not at 100%

186
Laravel throws only one set of data from the Database

Laravel throws only one set of data from the Database

I have this code in Laravel but it only gives me one set of data although it should return many data

142
PHP Getting MYSQL data from checked box

PHP Getting MYSQL data from checked box

I am creating a form to either approve or deny a time-off requestSo this will bring up a table with rows of data of time off requests

153