How to count two related dates in Laravel using Query Builder?

Question

I have this list of dates

I want to count the times that the time is higher than 24 hours between each dates in a row... tried with this ..

$tiempo1  = DB::table('incidencia_tiempo')
            ->whereTime('time', '>', '0000-00-00 24:00:00')
            ->count();

Beetween time and created at... I would like to count... if beetween the two times the hours are higher than 24 hours then add +1 to the count

I have to make a comparation with created_at and time and I don't know how

Answer 1

Use TIMESTAMPDIFF() to get the time between two datetime,

If you want to count the time high than the created_at 24 hours

$count  = DB::table('incidencia_tiempo')
            ->where(DB::raw('TIMESTAMPDIFF(HOUR, time, created_at)'), '>', 24)
            ->count();

if you want to count the time difference between two datetimes high than 24 hours:

$count  = DB::table('incidencia_tiempo')
            ->where(DB::raw('ABS(TIMESTAMPDIFF(HOUR, time, created_at))'), '>', 24)
            ->count();