Ways to find average time to state transition: using Eloquent or DB query

Question

I have a table named state_histories with the following content:

Entity type	entity_id	state	Creation time
bill	1	new	2020-04-16 06:24:50
bill	1	Claim	2020-04-16 07:24:50
bill	2	new	2020-04-16 07:34:50
bill	2	Hold down	2020-04-16 07:44:50
bill	1	Payment	2020-04-16 08:24:50
bill	2	Claim	2020-04-16 09:34:50

I want to use Eloquent or DB query to find out the average shipping time of invoices from NEW status to CLAIM status.

For the above output, it should be 90 minutes.

Answer 1

Before answering, it would be better to consider a way that doesn't require creating a new row for the new status of the invoice, maybe add some columns to reference the time of the status change, so to speak You can get created_at and assign to Dayname or Carbon In Carbon, use createFromFormat for assignment so you can do it for new and claim Then there is a method called diffInSeconds that you can call to get the differences of an entity. Do this for all entities and finally add their numbers to get the total number of distinct entities