How to fetch the earlier record from mysql query compare with date?

Question

I am trying to fetch the closest record of if date not matched in database. But somehow it is not working or you can say giving me the wrong information.

Below is my code that you can check and help me to find the bug.

        $atg = @DB::table('mc_atginfo')
            ->where('SiteId', Input::get('siteId'))
            ->where('TankId', Input::get('tankId'))
            ->orderByRaw('ABS(TIMESTAMPDIFF(SECOND, ATGDateTime,?))', [$inputDateTimeFormatted])
            ->orderBy('ATGDateTime', 'desc')
            ->first();
        return Response::json($atg);
    }

Any solution appreciated!

Answer 1

What are you trying to get? please be more descriptive, as it is not clear what you want to get.

If all that you want is the closest record to a date column you have in the table, you would just do:

->where('date_in_database', <=, $dateFromTheUser)
->orderBy('date_in_database', desc)
->first();

Answer 2

I would advice of processing the records yourself if there is no date match with Carbon. you can create an array of the record id, date, and diffence in days from the date you are querying. after that you can sort the array with the numbers of days, take the first x records, then get their data with the id in the array. example array:

$records = [
         ['id_1','23-03-2001',2]
         ['id_2','23-03-2009',2561]
         ['id_3','23-03-2017',4578]
         ['id_4','24-03-2001',1] 
];      

sorted array:

$records = [
         ['id_4','24-03-2001',1] 
         ['id_1','23-03-2001',2]
         ['id_2','23-03-2009',2561]
         ['id_3','23-03-2017',4578]         
]; 

then if you want the first only record