Calculate Avg Sum of days from column with type DATE in yii2 with diiference from today date

Question

I need help with query or way to do with single query in yii2.

Application is in yii2 , working on admin dashboard. I need to show the Avg days of all products in stock from today date that has been in system which are enabled (in short average age of inventory that is in stock).

NOTE : Just a single avg total required for column to show count on dashboard as a result.

I have a table with column stock_date , that stores in DateTime format Y-m-d H:i:s.

Formula

1. ( STOCK_DATE - TODAY DATE ) will give you age of single product in system.
2. Total SUM of column `stock_date' / Total number of products that are enabled will give you average days.

Table columns:

• inventory_id (primary key)
• status (1 for enabled)
• stock_date (datetime)

stock_date column screenshot

$AvgStatsCount = Inventory::find()
    ->select('sum(stock_date ) as stockdate')
    ....
    ->all();

Anyone know how to do that in right way ?

Posting Answer :  working code for me : 

$query = new Query; 
$query->from('mytable')->where(['status' => 1]); 
$AvgStatsCount = $query->average('DATEDIFF(stock_date, CURRENT_DATE())');

Answer 1

For this i recommend to use yii\db\Query instead of your model class that extends from ActiveRecord. With Query you can use methods like average, min and max that take a column name or SQL expression string as param and return the max, min or average value from the query.

To get the difference of stock_date - today i don't understand what format you are using, so i can't help you like this with that.

Edit:

So with stock_date in Y-m-d H:i:s date format, you can do something like this with yii\db\Query class:

use yii\db\Query;
...
$query = new Query;
$query->from('myTable');
$query->where(['>', 'stock_date', date('Y-m-d')]);
$average = $query->average('DATEDIFF(`stock_date`, CURRENT_DATE())');