Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

fbc's avatar
Level 2

How to query the last 12 months worth of data? Instead of every month this year.

I need to make a query of the last 12 months OF DATA and sums the readings for each month so that I can output this to a chart. This is what I currently use now:

        if (Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')->count() > 0) {

            $huffdash_energydata = [];
            $i=12;
            while ($i >= 0){
                $reading =  Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')
                    ->where('time','>=',Carbon::now()->startOfMonth()->subMonth($i)->timestamp)
                    ->where('time','<=',Carbon::now()->endOfMonth()->subMonth($i)->timestamp)
                    ->pluck('kwh')->sum();
                // Convert KWH to GJ and add to array
                array_push($huffdash_energydata, $reading*.0036);

                $i--;
            }


            $monthly_formattedDates = [];
            $i=12;
            while ($i >= 0){
                $reading =  Carbon::today()->subMonths($i)->format('F');
                array_push($monthly_formattedDates, $reading);

                $i--;
            }

            return [$monthly_formattedDates, $huffdash_energydata];
        }

This method is kinda bad because if there is now data for the last 6 months it's going to show a big gap in the charts.

I thought of doing an:

if ($reading ==0)
{
continue
}

I think I'm closer now I combined the two:

        if (Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')->count() > 0) {

            $huffdash_energydata = [];
            $monthly_formattedDates = [];
            $i=12;
            while ($i >= 0){
                $reading =  Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')
                    ->where('time','>=',Carbon::now()->startOfMonth()->subMonth($i)->timestamp)
                    ->where('time','<=',Carbon::now()->endOfMonth()->subMonth($i)->timestamp)
                    ->pluck('kwh')->sum();
                // Convert KWH to GJ and add to array
                if ($reading == 0){
                    continue;
                }
                // Convert KWH to GJ and add to array
                array_push($huffdash_energydata, $reading*.0036);
                $reading =  Carbon::today()->subMonths($i)->format('F');
                array_push($monthly_formattedDates, $reading);

                $i--;
            }

            return [$monthly_formattedDates, $huffdash_energydata];

Nope this still does not help me.. I think I have to determine last month I do have data for and figure out how to count back from there.

0 likes
2 replies
jlrdw's avatar
    public function veryTemp()
    {

        $page = Request::input('page', '1');
        $bdate = Request::input('begindate');  // got from datepicker with correct format.
        $edate = Request::input('enddate');

        // rest of query
Talinon's avatar
Talinon
Best Answer
Level 51

@fbc So you want your results to begin with the month which has the latest data?

$lastestMonth = Utility_Meter_Readings::where('property_id', $property_id)->latest('time')->first()->time;

Then you can start working backwards. If time is already a carbon instance, you're ready to go. Otherwise you might need parse:

Carbon::parse($latestMonth)->startOfMonth()->subMonth($i)->timestamp

Please or to participate in this conversation.