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 |
+----------+---------------------+---------+----------+