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
2
Answers
Here is how I managed to calculate it :
I made use of the function
LEAD
Fiddle: https://www.db-fiddle.com/f/ckqtYy3EuASF4RdF9dSEcv/4