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.
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