CODEWITHSUNDEEP
sqlnewrelicnrql
Craig
Craig

Reputation: 1985

How to calculate average difference between timestamps within same table?

I have data in a New Relic table as follows:

id timestamp status
A 2024-12-01 11:36:00 Started processing step 1
B 2024-12-01 11:37:00 Started processing step 1
B 2024-12-01 11:38:00 Started processing step 2
A 2024-12-01 12:01:00 Started processing step 2
C 2024-12-01 12:36:00 Started processing step 1
C 2024-12-01 12:41:00 Started processing step 2
B 2024-12-01 13:36:00 Started processing step 3
A 2024-12-01 13:46:00 Started processing step 3
C 2024-12-01 13:56:00 Started processing step 3

Processing time is defined as time elapsed since the final step (step 3) from the first step (step 1).

I want to find the average processing time over all process IDs

So for process A, it would be 2024-12-01 13:46:00 - 2024-12-01 11:36:00 = 2:10, and similarly calculated for processes B and C. Now I'm looking for the average of these processing times.

How can this be accomplished in NewRelic?

Upvotes: 0

Views: 62

Answers (2)

samhita
samhita

Reputation: 3155

Another approach using SELF JOIN, sharing the example in mysql, you would need to change some of the syntax

Fiddle

SELECT 
    AVG(TIMESTAMPDIFF(MINUTE, start_time, end_time)) AS average_processing_time_minutes
FROM (
    SELECT 
        t1.id,
        MIN(t1.timestamp) AS start_time,
        MAX(t2.timestamp) AS end_time
    FROM 
        processing_table t1
    JOIN 
        processing_table t2 
    ON 
        t1.id = t2.id 
    WHERE 
        t1.status = 'Started processing step 1'
        AND t2.status = 'Started processing step 3'
    GROUP BY 
        t1.id
) AS processing_times;

Output

average_processing_time_minutes
109.6667

Upvotes: 0

ValNik
ValNik

Reputation: 5673

Duration for process can be calculated as max(time)-min(time). Then group this duration over all processes and calculate average.

Date and time processing is DBMS specific. You can convert duration to seconds and calcelate average value.

See example (for SQL Server)

id timestamp status
A 2024-12-01 11:36:00.000 Started processing step 1
B 2024-12-01 11:37:00.000 Started processing step 1
B 2024-12-01 11:38:00.000 Started processing step 2
A 2024-12-01 12:01:00.000 Started processing step 2
C 2024-12-01 12:36:00.000 Started processing step 1
C 2024-12-01 12:41:00.000 Started processing step 2
B 2024-12-01 13:36:00.000 Started processing step 3
A 2024-12-01 13:46:00.000 Started processing step 3
C 2024-12-01 13:56:00.000 Started processing step 3 
select id,min(timestamp) fromtime,max(timestamp) totime
  ,max(timestamp)-min(timestamp) duration
from test
group by id

Output - duration for every process

id fromtime totime duration
A 2024-12-01 11:36:00.000 2024-12-01 13:46:00.000 1900-01-01 02:10:00.000
B 2024-12-01 11:37:00.000 2024-12-01 13:36:00.000 1900-01-01 01:59:00.000
C 2024-12-01 12:36:00.000 2024-12-01 13:56:00.000 1900-01-01 01:20:00.000 
select count(*) cnt
  ,cast(cast(avg(cast(duration as float)) as datetime) as time) as avgduration
from(
   select id,min(timestamp) fromtime,max(timestamp) totime
     ,max(timestamp)-min(timestamp) duration
   from test
   group by id
)x
cnt avgduration
3 01:49:40.0000000

fiddle

Upvotes: 1

Related Questions