查询created_at时忽略秒

Question

I have a scheduled task that runs every 5 minutes that collects some stats on a server I run.

There is a small delay whilst it waits for the request to come back and so records are always being saved 2 or 3 seconds later. I.e the task runs at 2017-14-03 08:00:00, but the records are saved at 2017-14-03 08:00:03.

I am trying to pull the records out to display on a graph. The graph scales to the time period you want to look at (through hard coded buttons that refresh the graph with new data).

The first graph I am trying to do is one over the last 24 hours. Rather than bring back every 5 minute point for the last 24 hours, I just want one per hour. I have built a function to round down to the nearest hour and then get the last 24 hours based off that - it looks like this:

public function last24Hours()
{
    $times = [];
    $time = Carbon::now()->minute(0)->second(0);
    $i = 1;
    while($i <= 24)
    {
        array_push($times, $time->toDateTimeString());
        $time->subHour();
        $i++;
    }

    return $times;
}

Using the times returned, I am trying to query the model with whereIn() like so:

$stats = ServerTracking::whereIn('created_at', $this->last24Hours())->get();

The query runs, but nothing comes back - as the created_at time is a couple of seconds off from what I am querying.

I've hit a bit of a roadblock and cannot think of a way to get around this? Any ideas?

Answer 1

You can use selectRaw with a formatted date:

$stats = ServerTracking::selectRaw('foo,bar,DATE_FORMAT(created_at, "%Y-%m-%d %H:00:00") as hour_created')->get()->keyBy('hour_created');

All of the values in each hour will have the same hour_created, and keyBy will only keep one of them (from docs):

If multiple items have the same key, only the last one will appear in the new collection.

Just replace foo and bar with the other values you need. You'll either keep the 0:55 minute values, or the 0:00 minute values, depending on how you sort the query.

Come to think of it, you could use whereRaw to do it your way:

->whereRaw("DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') in (" .implode(",",$last24Hours). ")")