How to select every nth row in Laravel query builder

Question

I have an API that fetches data using Laravel query builder. I make a call to this API from a react app. I have a new row every 5 minutes in my database and I need to be able to retrieve the data for a day or for a period (like a week or two). Making this call makes my react app very slow, even when I only fetch the data for a single day. I need to be able to select every nth row which would improve performance (as well as the readability of the chart I create based on the data...).

Instead of retrieving every record (every 5 minutes) I want to be able to retrieve 1 every 12 for example (every 60 minutes)

The time (log_time in my DB) is stored in a weird way: 103500 being 10:35 AM. In want to use a modulo in my query but I cannot make it work...

$data = DB::connection('mysql')->table('my_table')
    ->select('value','log_date','log_time')
    ->where('id_user',$userId)
    ->where('log_time', mod 500 = 0)    // This is the line I cannot get to work
    ->orderBy('log_date')
    ->orderBy('log_time')->get();

I tried different things for that line. Some were desperate attempts...

->where('log_time', mod 500 = 0)   // unexpected '500', expecting ')' 
->where('log_time mod 500 = 0')    // Unknown column 'log_time mod 500 = 0'
->where('log_time mod 500', 0)     // Unknown column 'log_time mod 500'
->where('log_time', % 500 = 0)     // unexpected '%', expecting ')' 
->where('log_time % 500 = 0')      // Unknown column 'log_time % 500 = 0'
->where('log_time % 500', 0)       // Unknown column 'log_time % 500'
->where('log_time', mod 500, 0)    // unexpected '500', expecting ')' 
->where('log_time', 'mod 500', 0)  // only return result for 'log_time' = 0 (midnight)
->where('log_time', % 500, 0)      // unexpected '500', expecting ')'
->where('log_time', '% 500', 0)    // only return result for 'log_time' = 0 (midnight)
->where('log_time', MOD(500,0))    // Call to undefined function MOD()

I also tried with ->whereRaw which didn't work either.

Answer 1

I cannot try it out at the moment but maybe a whereRaw could help?

->whereRaw('log_time MOD 500 = 0')

Answer 2

Actually I was having the same issue and the accepted answer is not doing what it has to, but this is sorting my problem:

• in my case I have stored logs every minute, but I want to get data only each 15 minutes (60 seconds * 15):

->whereRaw('MOD(UNIX_TIMESTAMP(log_time), 60 * 15) = 0')

• if you want to get data f.e. each hour, then 60 * 60:

->whereRaw('MOD(UNIX_TIMESTAMP(log_time), 60 * 60) = 0')

This solution worked for me exactly in many possible variants.