Calculate time duration while iterating on the same column

Would it be possibile to calculate the daily sum of hours based on the difference between time present in same colums?

date	time
2017-11-08	08:25:57
2017-11-08	12:31:59
2017-11-08	13:28:42
2017-11-08	17:34:13
2017-11-09	08:28:08
2017-11-09	12:31:15
2017-11-09	13:20:13

I can get separate data for enditime and startime with this query:

SELECT MAX(time) AS "endtime",
       MIN(time) AS "startime",
       MAX(time)-MIN(time) as "totalneedtocalculatepause"
WHERE (date = '2017-11-08'::date )
FROM history ts

but startpause and endpause are missing:

endtime	startime	totalneedtocalculatepause
17:34:13	08:25:57	09:08:16

The correct sequence should be:

day         startpause   starttime    endtime     endpause
2017-11-08: (12:31:59 - 08:25:57) + (17:34:13 - 13:28:42)

I'm using PostgreSQL 13.

Solution

Given the understanding that your records are coupled and consecutive values represent start-end periods, you can use a ROW_NUMBER function, divided by 2, and ceiled, to identify the couples (partitions). Then use your query, while partitioning on the new generated column.

WITH cte AS (
    SELECT *, CEIL((ROW_NUMBER() OVER(PARTITION BY date ORDER BY time)+1)/2) AS rn 
    FROM history
)
SELECT MAX(time) AS "endtime",
       MIN(time) AS "startime",
       CAST(MAX(time)-MIN(time) AS time) AS "totalneedtocalculatepause"
FROM cte
WHERE (date = '2017-11-08'::date)
GROUP BY rn

"Output":

endtime	startime	totalneedtocalculatepause
12:31:59	08:25:57	04:06:02
17:34:13	13:28:42	04:05:31

Check the demo here.

If you want the overall summary, you can apply a further aggregation:

WITH cte AS (
    SELECT *, CEIL((ROW_NUMBER() OVER(PARTITION BY date ORDER BY time)+1)/2) AS rn 
    FROM history
), cte2 AS (
    SELECT MAX(time) AS "endtime",
           MIN(time) AS "startime",
           CAST(MAX(time)-MIN(time) AS time) AS "totalneedtocalculatepause"
    FROM cte
    WHERE (date = '2017-11-08'::date)
    GROUP BY rn
)
SELECT MAX(endtime) AS "endtime",
       MIN(startime) AS "startime",
       CAST(SUM(totalneedtocalculatepause) AS TIME) AS "totalneedtocalculatepause"
FROM cte2

"Output":

endtime	startime	totalneedtocalculatepause
17:34:13	08:25:57	08:11:33

Check the demo here.