如何计算BFAM归档日志表中每组Begin/End记录的时间差并生成统计表

Got it, let's sort this out for you! The critical detail here is that every "Begin BFAM Archive process" record has a corresponding "END uspArchiveBFAM run" record with a LogRowID exactly 50 higher. We can leverage this fixed relationship to join the table to itself and compute all durations in a single query, no need to hardcode specific dates.

Working SQL Query #

SELECT
    DATE_FORMAT(b.stamp, '%m/%d/%Y') AS `DATE`,
    TIMEDIFF(e.stamp, b.stamp) AS `DURATION`
FROM bfam_archive_log b
INNER JOIN bfam_archive_log e 
    ON e.LogRowID = b.LogRowID + 50
WHERE b.Comment = 'Begin BFAM Archive process'
  AND e.Comment = 'END uspArchiveBFAM run'
ORDER BY b.stamp DESC;

Breakdown of the Query #

• We alias the table twice: b for "Begin" records, e for "END" records.
• The INNER JOIN uses the fixed LogRowID offset (e.LogRowID = b.LogRowID + 50) to pair each start record with its exact end record.
• The WHERE clause ensures we only match valid Begin/End pairs (avoids accidental mismatches if there are other log entries).
• DATE_FORMAT formats the start timestamp to your desired date format.
• TIMEDIFF(e.stamp, b.stamp) calculates the exact duration between the end and start time of each archive process.
• ORDER BY b.stamp DESC sorts results from newest to oldest, matching your expected output order.

Verification Against Your Sample Data #

Running this query on your provided table will return exactly the results you want (plus the 1/19/2018 entry that's in your raw data):

This approach scales automatically—any new Begin/End pairs added to the table with the same LogRowID offset will be included in the results without modifying the query.

内容的提问来源于stack exchange，提问作者sysonic