Get the sum of last week’s records

Question

Hello, I am studying Laravel I’m using the version 5.4, I need some help 'cause I’m not getting back the total records from last week’s database, I managed to return the total, and the total of the month, but the total of the last week.

Making it clear that what I couldn’t get was to return the total of data from the last 7 days of the database.

Here is my Controller:

public function index(){
        $users = DB::table('clientes')->count(); 

        $semana = date("d")-7;
        $usersemanal = DB::table('clientes')->whereDate('created_at', $semana)->count();

        $mes = date('m');
        $totalusers = DB::table('clientes')->whereMonth("created_at",$mes)->count();    

        return view('dashboard', compact('users', 'totalusers', 'usersemanal'));
    }

This is the part of the code I made to return last week but I can’t get anywhere.

Some solution or other way to make this return?

$semana = date("d")-7;
        $usersemanal = DB::table('clientes')->whereDate('created_at', $semana)->count();

Thanks for your help.

Answer 1

Since you are already using the Query Builder you can use the following syntax:

$usersemanal = DB::table('clientes')
      ->whereRaw('created_at >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 1 WEEK))')
      ->count();

Or if you prefer to bring only the total

$usersemanal = DB::table('users')
        ->select(DB::raw('COUNT(*) as total'))
        ->whereRaw('created_at >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 1 WEEK))')
        ->first();
// debug
dd($usersemanal);

References

• Laravel whereRaw
• Laravel Retrieving A Single Row / Column From A Table

Answer 2

The right answer may vary according to the type of data your created_at contains, if it is dateTime or date use the UNIX_TIMESTAMP will not return the expected result when using it, unless you use it with the FROM_UNIXTIME.

Why? When using the unix_timestamp, you will be comparing your field with a different type (date/datetime with timestamps), see in the image:

Soon, you’ll be doing the following:

SELECT * FROM clientes 
         WHERE created_at >= 1559780940
         //onde o created_at será 2019-06-12 00:00:00 por exemplo

See how it would be used with the from_unixtime

Where:

SELECT * FROM clientes 
         WHERE created_at >= '2019-06-05 21:32:04'
         //onde o created_at será 2019-06-12 00:00:00 por exemplo

With this, you can use the UNIX_TIMESTAMP in partnership with the FROM_UNIXTIME:

$usersemanal = DB::table('clientes')
                        ->whereRaw('created_at >= FROM_UNIXTIME(UNIX_TIMESTAMP(SUBDATE(NOW(),INTERVAL 1 WEEK)))')
                        ->count();

Or try alternative and simpler ways that will also return the result you expect:

$usersemanal = DB::table('clientes')->whereRaw('`created_at` > (NOW() - INTERVAL 7 DAY)')->count();

$usersemanal = DB::table('clientes')->whereRaw('`created_at` > (NOW() - INTERVAL 1 WEEK)')->count();

$usersemanal = DB::table('clientes')->whereRaw('`created_at` > SUBDATE(NOW(), INTERVAL 1 WEEK)')->count();

References:

SUBDATE()

unix_timestamp(), from_unixtime(), date_add(), date_sub() and interval Concept in Mysql

Mysql UNIX_TIMESTAMP() Function

Database: Migrations

Where Clauses - Laravel