I have a query to get the bookings that were done on a specific date which is run by a cronjob (every minute). However, I cannot seem to query bookings rounded off to the nearest minute. How can this be achieved?

Booking::where('completed_at', now())->get();

This query only allows to compare

Y-m-d H:i:s

How can I compare

Y-m-d H:i

CodePudding user response：

There's two solutions that come to mind:

Booking::whereBetween('completed_at', [now()->startOfMinute(), now()->endOfMinute() ])->get();

This gets everything within the same minute

Booking::where(\DB::raw("DATE_FORMAT(completed_at, '%Y-%m-%d %H:%i')"), now()->format('Y-m-d H:i'))->get();

This matches formatted dates.

I prefer the 1st approach because you don't need to worry about formatting issues and it can also make use of indexes.

CodePudding user response：

Asserting that now() will happen exactly every 60.00 seconds is wrong, cronjob run every 60 seconds /- few ms, and PHP process before calling now() (and between 2 calls of now()) takes few µs to few ms depending on the operations.

So you should have a field like processed_by_the_cronjob which is false by default and you set it to true once your CRON job queried it.

Then you select:

Booking::where('completed_at', '<=', now())->andWhere('processed_by_the_cronjob', false)->get();