One hour interval - Laravel

user_8411150 0
Viewed 309

I'm trying to select all records based on date/time. I have this timestamps in 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

I have a code in the controller getting all records:

    $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();

The goal is to select only records in every hour like this:

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

I get error when use:

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

The error is:

Undefined index: recordtime
laravel postgresql
2 Answers

You could use LIKE to get the records with timestamps that have no minutes or seconds and if there's no other processing required.

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

Also, your query could probably work with this slight fix (AS column_name)

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

Please try something like this:

$result = Tablemodel1::select([
                 DB::raw('count(table2.info) as counted_info'),
                 DB::raw('DATE_FORMAT(table1.recordtime, "%H") hour'),
          ])
          ->whereBetween('table1.recordtime', $dateScope)
          ->groupBy('hour')
          ->orderBy('hour')
          ->get();

You should do the next steps:

  1. Format date
  2. Group by formatted date (Example: DATE_FORMAT(orders.created_at, "%b %d") day)
  3. Sort (optional)
  4. collect data

All possible formates described here: enter link description here

user_46810930
Related