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.