CODEWITHSUNDEEP
sqldatabasepostgresql
Abdul Salam
Abdul Salam

Reputation: 238

SQL query to calculate average time duration between different events

I need help on building SQL query:

Here is my postgres table I use to store the test run statistics.

CREATE TABLE logs
(
  id bigint NOT NULL,
  test_id int,
  state text,
  start_date timestamp with time zone,
  end_date timestamp with time zone,
  CONSTRAINT logs_pkey PRIMARY KEY (id)
)

This table contains test start time, end time and status. I need to calculate i use due to a test failure. i.e time interval between a test failure and the next immediate test start.

i.e for each test failed records, get the end_date and get the start_date of the next immediate record for the same test. calculate the time differences. Sum the duration for all such failed records and devide by number of failures. to get the average.

Example :

enter image description here

Let's say i have following table of data, only 5 columns , last column Duration in failed state should be part of my query.

i.e i have totally 3 failed records two for test 1 and one for test 2. so total time = 130minutes/3 = around 43.3 minutes will be in secs.

can somebody get me the SQL query for this ?

Upvotes: 2

Views: 2475

Answers (1)

user330315
user330315

Reputation:

As already mentioned this can easily be done using a window function:

select id,  
       test_id,
       start_date, 
       end_date,
       state,
       case 
         when state = 'FAIL' then lag(start_date) over (partition by test_id order by start_date desc) - end_date
         else null
       end as time_diff
from logs
order by test_id, start_date desc;

SQLFiddle: http://sqlfiddle.com/#!15/0d40f/2

Upvotes: 1

Related Questions