Question 246976 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    What about lag instead of ceil?
    i.e. if not all rows are pairs?

    I’ve edited the fiddle to manage more days:

    https://www.db-fiddle.com/f/jsHiga6b2BmgmacrsadkCF/0

    SELECT *, lag(time) OVER(PARTITION BY date ORDER BY time) AS "starttime"  
    FROM history;

    First split time columns

    WITH cte AS (

  SELECT *, lag(time) OVER(PARTITION BY date ORDER BY time) AS "starttime"  
    FROM history
)
SELECT 
       CAST("time"-"starttime" AS time) AS "segment"
FROM cte
WHERE (date >= '2017-11-08'   AND date <  '2017-12-09'      )
group by date, time,starttime
;

    Then calculate single segments of time.

    WITH cte AS (
    SELECT *, lag(time) OVER(PARTITION BY date ORDER BY time) AS "starttime"  
    FROM history
), cte2 AS (
    select 
    CAST("time"-"starttime" AS time) AS "segment"
    FROM cte
WHERE (date >= '2017-11-08'   AND date <  '2017-12-08'      )
    group by date, time,starttime
)
SELECT        
       CAST(SUM(segment) AS TIME) AS "total"
       
       
FROM cte2
where segment > interval '1 hour';

    Then get totals, to count the pause I expect that this will not be more than 1 hour so excluding lesser intervals should give you the correct total.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search