Home > Mobile >  Getting the cycle time of items in PostgreSQL
Getting the cycle time of items in PostgreSQL

Time:12-24

I got a table of data which have log the history of some items. The items evolves over time and are passing different states like "Submitted", "In Verification", "Completed":

ID Modified Date Status
123 01.01.2021 12:01 Submitted
123 02.01.2021 12:02 In Verification
123 03.01.2021 12:03 Completed
345 06.01.2021 12:04 Submitted
345 04.01.2021 12:05 In Verification
345 19.01.2021 18:06 Completed

I want to know how many days each item was in a specific state. Is there a way to do this with PostgreSQL?

CodePudding user response:

You can use Window functions to get Access to the Following row and calculate the difference between the 2 Fields. https://www.postgresqltutorial.com/postgresql-lead-function/

Without testing, it should look like something like that:

select *, 
Datediff(dd,[Modified Date],lead([Modified Date],1,GETDATE()) 
over 
(order by ID, [Modified Date])) as [Days between]
from log 
order by ID, [Modified Date]

With that you get the next Value from your table in your actual row and you can calculate with it. For Default Value actuall Date is used, but you can use every other Date you want.

CodePudding user response:

You can use the analytical function LEAD(). In your question is not clear that the statuses follow a specific workflow or they are meaningless, so the query below is generic.

You can do:

select *,
  lead(modified_date) over(partition by id order by modified_date) 
  - modified_date as diff
from t

Result:

id   modified_date             status           diff                               
---- ------------------------- ---------------- ---------------------------------- 
123  2021-01-01T12:01:00.000Z  SUBMITTED        {"days":1,"minutes":1}             
123  2021-01-02T12:02:00.000Z  IN_VERIFICATION  {"days":1,"minutes":1}             
123  2021-01-03T12:03:00.000Z  COMPLETED        null                               
345  2021-01-04T12:05:00.000Z  IN_VERIFICATION  {"days":1,"hours":23,"minutes":59} 
345  2021-01-06T12:04:00.000Z  SUBMITTED        {"days":13,"minutes":2}            
345  2021-01-19T12:06:00.000Z  COMPLETED        null                               

See running example at DB Fiddle.

  • Related