laravel - 一小时间隔 - Laravel

Question

我正在尝试根据日期/时间选择所有记录。
我在 postgreSQL 中有这个时间戳:

13/12/2020 11:00:00
14/12/2020 11:31:00
14/12/2020 12:30:00
14/12/2020 13:00:00
15/12/2020 02:00:00

我在 Controller 中有一个获取所有记录的代码:

    $start_date = date('d/m/Y 00:00:00');
    $end_date = date('d/m/Y 23:59:59');

    if($request->start_date != '' && $request->end_date != '')
    {
        // if user fill dates
        $dateScope = array($request->start_date, $request->end_date);
    } else {
        // default load page - today
        $dateScope = array($start_date, $end_date);
    };

  $results  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('table1.recordtime','table2.info')
    ->orderBy('recordtime', 'ASC')
    ->get();

目标是每小时只选择记录，如下所示:

13/12/2020 11:00:00
14/12/2020 13:00:00
15/12/2020 02:00:00

使用时出现错误:

  $results  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('extract(hour from table1.recordtime)','table2.info')
    ->orderBy('recordtime', 'ASC')
    ->get();

错误是:

Undefined index: recordtime

Answer 1

您可以使用 LIKE获取时间戳没有分钟或秒的记录，如果不需要其他处理。

$results  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->where('table1.recordtime','LIKE', '%:00:00%')
    ->selectRaw('table1.recordtime','table2.info')
    ->orderBy('recordtime', 'ASC')
    ->get();

此外，您的查询可能适用于这个轻微的修复( AS column_name )

$results  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('extract(hour from table1.recordtime) AS recordtime','table2.info')
    ->orderBy('recordtime', 'ASC')
    ->get();