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

Calculate duration between Phase – Postgresql

I have the following history table (record user action):

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| parent_id | property_names                                                          | changed_property                                                                                                                                                                                                                                                                                | time_c        | outcome |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 123456    | {PhaseId,LastUpdateTime}                                                | {"PhaseId":{"newValue":"Fulfill","oldValue":"Approve"},"LastUpdateTime":{"newValue":1671027321749,"oldValue":1671027321170}}                                                                                                                                                                    | 1671027321749 | success |
| 123456    | {PhaseId,LastUpdateTime,ApprovalStatus}                                 | {"PhaseId":{"newValue":"Approve","oldValue":"Log"},"LastUpdateTime":{"newValue":1671011168777,"oldValue":1671011168043},"ApprovalStatus":{"newValue":"InProgress"}}                                                                                                                             | 1671011168777 | success |
| 123456    | {LastUpdateTime,PhaseId,Urgency}                                        | {"LastUpdateTime":{"newValue":1671011166077},"PhaseId":{"newValue":"Log"},"Urgency":{"newValue":"TotalLossOfService"}}                                                                                                                                                                          | 1671011166077 | success |
| 123456    | {LastUpdateTime,ApprovalStatus}                                         | {"LastUpdateTime":{"newValue":1671027321170,"oldValue":1671027320641},"ApprovalStatus":{"newValue":"Approved","oldValue":"InProgress"}}                                                                                                                                                         | 1671027321170 | success |
| 123456    | {PhaseId,LastUpdateTime,ExecutionEnd_c}                                 | {"PhaseId":{"newValue":"Accept","oldValue":"Fulfill"},"LastUpdateTime":{"newValue":1671099802675,"oldValue":1671099801501},"ExecutionEnd_c":{"newValue":1671099802374}}                                                                                                                         | 1671099802675 | success |
| 123456    | {PhaseId,LastUpdateTime,CompletionCode}                                 | {"PhaseId":{"newValue":"Review","oldValue":"Accept"},"LastUpdateTime":{"newValue":1671099984979,"oldValue":1671099982723},"CompletionCode":{"oldValue":"CompletionCodeAbandonedByUser"}}                                                                                                        | 1671099984979 | success |
| 123456    | {PhaseId,LastUpdateTime,ExecutionStart_c}                               | {"PhaseId":{"newValue":"Fulfill","oldValue":"Review"},"LastUpdateTime":{"newValue":1671100012012,"oldValue":1671099984979},"ExecutionStart_c":{"newValue":1671100011728,"oldValue":1671027321541}}                                                                                              | 1671100012012 | success |
| 123456    | {UserAction,PhaseId,LastUpdateTime,ExecutionEnd_c}                      | {"UserAction":{"oldValue":"UserActionReject"},"PhaseId":{"newValue":"Accept","oldValue":"Fulfill"},"LastUpdateTime":{"newValue":1671100537178,"oldValue":1671100535959},"ExecutionEnd_c":{"newValue":1671100536730,"oldValue":1671099802374}}                                                   | 1671100537178 | success |
| 123456    | {PhaseId,Active,CloseTime,LastUpdateTime,LastActiveTime,ClosedByPerson} | {"PhaseId":{"newValue":"Close","oldValue":"Accept"},"Active":{"newValue":false,"oldValue":true},"CloseTime":{"newValue":1671101084529},"LastUpdateTime":{"newValue":1671101084788,"oldValue":1671101083903},"LastActiveTime":{"newValue":1671101084529},"ClosedByPerson":{"newValue":"511286"}} | 1671101084788 | success |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Description of the column :

parent_id : link to the parent element

property_names : property having a modification

changed_property : New value for the properties. for ex:

{
   "PhaseId":{
      "newValue":"Fulfill",
      "oldValue":"Approve"
   },
   "LastUpdateTime":{
      "newValue":1671027321749,
      "oldValue":1671027321170
   }
}

The property PhaseId change the value from Approve to Fulfill

time_c : Unix Timestamp of the update

outcome : Status of the update

My goal is to calculate the duration of each phase.
Expected output :

------------------------------------------------------------
| parent_id | Log  | Approve  | Fulfill  | Accept | Review |
------------------------------------------------------------
| 123456    | 2700 | 16152972 | 73006092 | 729914 | 27033  | 
------------------------------------------------------------

Log : 1671011168777 - 1671011166077 = 2700

Approve : 1671027321749 - 1671011168777 = 16152972

Fulfill : (1671100537178 - 1671100012012) + (1671099802675 - 1671027321749) = 73006092

Accept : (1671101084788 - 1671100537178) + (1671099984979 - 1671099802675) = 729914

Review : 1671100012012 - 1671099984979 = 27033

At this moment, I’m able to retreive the new and old value of the PhaseId and convert the unix timestamp to datetime.
My issue is how to calculate the duration of a phase using SQL

My current SQL request :

SELECT * FROM
(SELECT 
  parent_id,
  property_names,
  changed_property,
  time_c,
  to_char(to_timestamp(time_c/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd hh24:mi:ss') AS "time to datetime",
  outcome,
  changed_property::json->'PhaseId'->> 'newValue' AS "PhaseId (new)",
  changed_property::json->'PhaseId'->> 'oldValue' AS "PhaseId (old)"
FROM history 
WHERE array_to_string(property_names, ', ') like '%PhaseId%'
ORDER BY time_c DESC) AS temp_c
/*
WHERE "PhaseId (new)" = 'Close'
OR "PhaseId (old)" = 'Close'
*/

Result (irrevelant data hidded) :

-----------------------------------------------------------------------------------
| parent_id | time_c        | time to datetime    | PhaseId (new) | PhaseId (old) |
-----------------------------------------------------------------------------------
| 123456    | 1671101084788 | 2022-12-15 11:44:44 | Close         | Accept        |
| 123456    | 1671100537178 | 2022-12-15 11:35:37 | Accept        | Fulfill       |
| 123456    | 1671100012012 | 2022-12-15 11:26:52 | Fulfill       | Review        |
| 123456    | 1671099984979 | 2022-12-15 11:26:24 | Review        | Accept        |
| 123456    | 1671099802675 | 2022-12-15 11:23:22 | Accept        | Fulfill       |
| 123456    | 1671027321749 | 2022-12-14 15:15:21 | Fulfill       | Approve       |
| 123456    | 1671011168777 | 2022-12-14 10:46:08 | Approve       | Log           |
| 123456    | 1671011166077 | 2022-12-14 10:46:06 | Log           | null          |
-----------------------------------------------------------------------------------

DB fidle : https://www.db-fiddle.com/f/ckqtYy3EuASF4RdF9dSEcv/2

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is how I managed to calculate it :

    WITH temp AS (
    SELECT  parent_id,
        changed_property::json->'PhaseId'->> 'newValue' AS phase,
        time_c,
        LEAD(time_c,1) OVER (
            PARTITION BY parent_id
            ORDER BY parent_id,time_c
        ) next_time
    FROM history
    where 'PhaseId' = ANY(property_names) 
)   
SELECT  parent_id,
    phase,
    justify_interval(make_interval(secs =>SUM((next_time-time_c)/1000))) AS "Durations"
FROM temp
GROUP BY parent_id,phase
ORDER BY parent_id

    I made use of the function LEAD

    Fiddle: https://www.db-fiddle.com/f/ckqtYy3EuASF4RdF9dSEcv/4


  2. 0 
    select * from crosstab(
    '
        with ordered_changes as (select parent_id,
                                        time_c,
                                        changed_property::json -> ''PhaseId'' ->> ''newValue'' AS PhaseId_New,
                                        changed_property::json -> ''PhaseId'' ->> ''oldValue'' AS PhaseId_Old,
                                        property_names,
                                        changed_property,
                                        outcome
                                 from history
                                 where arraycontains(property_names, ARRAY [''PhaseId''])
                                 order by parent_id, time_c desc),
             all_stage_durations as (select oc.parent_id,
                                            oc.time_c - lag(oc.time_c, 1) over (order by time_c) as duration,
                                            oc.PhaseId_old,
                                            oc.time_c                                               end_ts,
                                            lag(oc.PhaseId_New, 1) over (order by time_c),
                                            lag(oc.time_c, 1) over (order by time_c)                start_ts
                                     from ordered_changes oc)
        select asd.parent_id, asd.PhaseId_old stage, sum(asd.duration) total_time
           from all_stage_durations asd
           where asd.PhaseId_old is not null
           group by asd.parent_id, asd.PhaseId_old
           order by parent_id, stage
    ',
    'select stage from (' ||
    'select distinct changed_property::json -> ''PhaseId'' ->> ''newValue'' AS stage from history union ' ||
    'select distinct changed_property::json -> ''PhaseId'' ->> ''oldValue'' AS stage from history ) a ' ||
    'where stage is not null order by stage'
)
as ct(parent_id int, Accept int, Approve int, Close int, Fulfill int, Log int, Review int)
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search