CODEWITHSUNDEEP
phpsqlyii2
user759235
user759235

Reputation: 2207

Yii2, get an average value from timestamps

I have a basic table with orders, which have a field thats called created_at which is in timestamp format and I want to get the avarage on how many orders have been created per day.

Found a other similar qestion about something like mine question whichI have posted below in the hope that everybody understand what im trying to do.

//not working
$q = new \yii\db\Query();
$q->from('shopping_cart');
$total = $q->average('DATEDIFF(`created_at`, CURRENT_DATE())');

Upvotes: 1

Views: 291

Answers (2)

Anton Rybalko
Anton Rybalko

Reputation: 1314

Method average in $q->average('DATEDIFF('created_at', CURRENT_DATE())'); just adds AVG(DATEDIFF('created_at', CURRENT_DATE())) to SQL command.

As Jiri Semmler said, what you want to do is about SQL not Yii.

All you need to do is find the count of records for the period you are interested in and divide it by the number of days of that period.

It can be something like

// Define period
$timeFrom = '2018-11-30';
$timeTo = '2018-12-02';

// Number of days for the period
$days = date_diff(date_create($timeFrom), date_create($timeTo))->format("%a");

// Query count of records between dates
$q = new \yii\db\Query();
$total = $q->from('order')
  ->where(['between', 'created_at', $timeFrom, $timeTo])
  ->count();

// Find average records per day
$average = $total / $days;

If you have Order model class:

// Query count of records between dates
$total = Order::find()
  ->where(['between', 'created_at', $timeFrom, $timeTo])
  ->count();

Upvotes: 0

Jiri Semmler
Jiri Semmler

Reputation: 421

I believe it is more SQL related problem than Yii2. What you need (if I have understood it correctly) is:

  1. count number of days from the beginning to today
  2. count all the rows
  3. divide these number to get the average.

I have tried this and it works fine

SELECT 
count(*) / (select round((unix_timestamp() - (select min(created_at) from table)) / 86400))
from table;

back to Yii2: I believe you have to build this query manually

\Yii::$app->db->createCommand()

Upvotes: 1

Related Questions