eloquent fetch records within recent 3 hours

Question

I need to fetch all the records which is inserted between past 3 hours to current(now). I am using laravel framework(eloquent orm).

I tried this found here

$lb = \DB::table('myTable')->whereRaw('created_at = DATE_ADD(NOW(), INTERVAL -3 HOUR');

But it return NULL. Is there any way I can do using eloquent but not Raw Query?

Any Help would be appreciated.

Solution 1

Laravel comes with Carbon, a nice library to handle dates, which can be used in combination with Eqlouent.

Example:

\DB::table('myTable')
    ->where('created_at', '>', 
        Carbon::now()->subHours(3)->toDateTimeString()
    );

More Information

For more fun date methods, check out these docs on Carbon http://carbon.nesbot.com/docs/#api-addsub

Solution 2

We can use PHP DateTime. Like this,

$date = new \DateTime();
$date->modify('-3 hours');
$formatted_date = $date->format('Y-m-d H:i:s');
$lb = \DB::table('myTable')->where('created_at', '>',$formatted_date);

In above code what we're doing is creating date string with PHP and using that in query.

Solution 3

add this scope to your model:

public function scopeRecent($query)
{
    return $query-> whereDate('created_at ' , '=',Carbon::today())
        ->whereTime('created_at' , '>',Carbon::now()->subHours(3));

}

then use the scope in controller :

    $posts= Post::recent()->pluck("id")->toArray();

Solution 4

Try This

$lb = \DB::table('myTable')->whereRaw('created_at >= DATE_SUB(NOW(), INTERVAL 3 HOUR)')

In your statement you are using = (206-07-27 11:30:00) instead >= (206-07-27 11:30:00)

View more solutions

Comments

[VijayRana]

this query is showing syntax error. syntax error, unexpected '3'

[VijayRana]

this query always returns null either I changes the hour value to more or less.

[Alok Patel]

We can also do the same using PHP DateTime, try my second method if you're allowed to do so.

[VijayRana]

Finally the second method works fine. Thanks @Alok Patel

[Chris]

@Ali Seyfi why the edit changing it from whereDate to where?

[eylay]

i dont exactly know, but it didnt work with whereDate and worked with where when i executed the code

[Chris]

@AliSeyfi The solution I posted is tested and documented - which is probably better for future readers than an edit that you don't know why it worked....

[Chris]

But if you can't explain why my version didn't work - especially considering its a documented Laravel feature, I don't understand why you would change it. Anyway, your call, it got approved