CODEWITHSUNDEEP
phpmysqllaraveleloquent
Parker Queen
Parker Queen

Reputation: 619

Select rows with timestamp of difference of 1 Hour or more

I have a table in a database and I want to fetch records from it but with a little complex condition.

Every 30 minutes, three records are added into the table. Now I want to retrieve the records but those with one hour difference. So basically, if 3 records are added at 1 PM and then 3 are added at 1:30 PM and then another 3 at 2PM, I want to be able to fetch the records added at 1PM and 2PM but leave out 1:30PM.

I would give my code but I am using Laravel and I think not everybody would be able to understand Eloquent, the query builder Laravel uses.

P.S:

public function hours24()
{
    $data = new Main();
    $temp = $data->order_by('updated_at', 'desc')->first();
    $time = strtotime($data->updated_at);
    $data = $data->where('updated_at', '>=', date('Y-m-d H:i', strtotime('-1 day')))->where('updated_at', '>=', $time + 3600)->get();
}

The problem with above is I am adding 3600 seconds everytime to $time which is a fixed time and so it will only work for the first row since after that each row would techincally be more than an hour apart. There seems no way to increase 3600 for each row.

Upvotes: 1

Views: 2742

Answers (1)

peterm
peterm

Reputation: 92845

If you're interested in records that are being inserted in a fixed time window - around the top of the hour, let's say +/- 5 min you can do something along the lines of

Main::whereRaw('updated_at >= NOW() - INTERVAL 1 DAY')
    ->where(function ($query) {
        $query->whereRaw('MINUTE(updated_at) > 54')
            ->whereRaw('MINUTE(updated_at) < 6')
    })
    ->get();

which translates to the following raw query

SELECT * 
  FROM main
 WHERE (MINUTE(updated_at) > 54
    OR MINUTE(updated_at) < 6)
   AND updated_at >= NOW() - INTERVAL 1 DAY

Here is dbfiddle demo

Upvotes: 1

Related Questions