Tuesday, May 23, 2017

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 that but I am using Laravel basically 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.



via Henry Spike

Advertisement