Odtnhj

I want to calculate time duration in minutes using PostgreSQL (phone calls).

-- Difference between 08:54:55 and 08:56:10 (time only)

SELECT DATE_PART('hour', '08:56:10'::time - '08:54:55'::time) * 60 +
DATE_PART('minute', '08:56:10'::time - '08:54:55'::time);
-- Result: 1

How can I modify this to work for the whole dataset? Start time (only hour and minute, seconds are always 00) and end time are in the same column Event_Time, grouped by Correlation_ID.

Event_Time Customer_ID Case_ID Event_ID Correlation_ID Line_ID Internal_ID
10:11:00 id1 1 start sd34 l3 456
10:13:00 id5 8 stop rt56 l3 456
10:15:00 id4 3 start tg84 l7 567
10:17:00 id7 5 start ty69 l4 678
10:21:00 id5 5 stop rt56 l7 678
10:31:00 id1 1 stop sd34 l4 567

I tried this solution, but it works only when I specify time http://www.sqlines.com/postgresql/how-to/datediff.

Thanks!

perhaps with a self join like this:

SELECT
 start."Correlation_ID",
 extract(epoch from stop."Event_time" - start."Event_time")/60
FROM
 some_table AS start
JOIN
 some_table AS stop
 ON start."Correlation_ID"=finish."Correlation_ID"
 AND stop."Event_ID"='stop'
WHERE 
 start."Event_ID"='start'
;

or with a group like this:

SELECT
 "Correlation_ID",
 extract(epoch from 
 max(CASE "Event_ID" WHEN 'stop' THEN "Event_time" END ) 
 -min(CASE "Event_ID" WHEN 'start' THEN "Event_time" END ) 
 ) /60
FROM
 some_table
JOIN
 grouP BY "Correlation_ID"
;