Calculate duration between Phase

Question

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

Answer 1

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)
;