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":

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.