How can i get the sum of a MySQL table's field for today date?

Question

I am building a simple shopping cart using PHP and as per title i want to get the sum (total earnings) of price field of orders table but only for today date.

I created the following function but i can get the total earnings for the last day there were earnings, not today. For example if Today is Friday and the last day which there were earnings are Monday($180), then the result i get is Monday:$180 instead of Friday:$0 which is what i want.

Here is what i have now:

    public function getTotalEarningsToday()
    {
        $records = (new Query())->select([
                'sum(price) as count',
                "from_unixtime(updated_at, '%Y-%m-%d') as day",
                 ])
            ->from(Orders::tableName())
            ->orderBy('day desc')
            ->groupBy('day')
            ->indexBy('day')
            ->limit(1)
            ->where(['status' => Orders::STATUS_COMPLETED])
            ->all();

        return $records;
    }

I tried to store the current date in a variable and do something like that:

   public function getTotalEarningsToday()
    {
        $todayDate = date("d.m.Y H:i");
        $records = (new Query())->select([
                'sum(price) as count',
                "from_unixtime(updated_at, '$todayDate') as day",
                 ])
            ->from(Orders::tableName())
            ->orderBy('day desc')
            ->groupBy('day')
            ->indexBy('day')
            ->limit(1)
            ->where(['status' => Orders::STATUS_COMPLETED])
            ->all();

        return $records;
    }

and now i get the today date inside the Array but still i can't get the right sum earnings records:

Array ( [count] => 61.35 [day] => 21.02.2022 09:18 ) )

It should be:

  Array ( [count] => 0 [day] => 21.02.2022 09:18 ) )

Any help would be very much appreciated.

Answer 1

I think you should use just day in this case, since time may vary. If your time is stored in the date() format you can do it like this:

$todayDate = strtotime(date("d.m.Y"));
$records = (new Query())->select([
        'sum(price) as count',
        'updated_at as day',
])
->from(Orders::tableName())
->orderBy('day desc')
->groupBy('day')
->indexBy('day')
->limit(1)
->where([
     'AND',
     'status' => Orders::STATUS_COMPLETED, 
     'updated_at' => $tadayDate]
)->all();

Or, as usual we have created_at and updated_at fields in the table, updated_at may be empty, in this case, reasonable will be to use created_at field to query results:

$todayDate = strtotime(date("d.m.Y"));
$records = (new Query())->select([
        'sum(price) as count',
        'created_at as day',
])
->from(Orders::tableName())
->orderBy('day desc')
->groupBy('day')
->indexBy('day')
->limit(1)
->where([
      'AND',
      'status' => Orders::STATUS_COMPLETED, 
      'created_at' => $tadayDate]
)->all();

Or use BETWEEN for the date, like it was mentioned in the comments.

updated_at >= '2022-02-21 00:00' AND updated_at <= '2022-02-21 23:59'

Answer 2

There is no need to use group by, order by, index by or limit when all you want is to get sum over one day.

$sum = Orders::find()
    ->where(new \yii\db\Expression(
        'DATE(FROM_UNIXTIME(`updated_at`)) = :today',
        [':today' => date('Y-m-d')]
    ))->sum('price');