Time-series aggregate readings

aggregate_reading.php

<?php

public function getAggregateReadings(\DateTime $from, \DateTime $to, \DateInterval $interval) {

        $from = clone $from;
        $to = clone $to;

        $seconds = date_create('@0')->add($interval)->getTimestamp();

        //Round to the correct block.
        $to->setTimestamp(floor($to->getTimestamp() / $seconds) * $seconds);
        $from->setTimestamp(floor($from->getTimestamp() / $seconds) * $seconds);

        //Man, stupid UNIX ts bullshit in mysql not in UTC
        $sql = '
          SELECT `timestamp`, FLOOR(UNIX_TIMESTAMP(CONVERT_TZ(`timestamp`, "+00:00", @@session.time_zone)) / :seconds) * :seconds AS rounded_timestamp, COUNT(`value`) / SUM(1 / `value`) AS average_value FROM sensor_reading
          WHERE sensor_id = :sensor_id
           AND `timestamp` BETWEEN :date_start AND :date_end
          GROUP BY rounded_timestamp;
          ORDER BY rounded_timestamp DESC';

        $stmt = DB::get()->getConnection()->prepare($sql);
        $stmt->execute(['sensor_id' => $this->sensor_id,
                        'date_start' => $from->format('Y-m-d H:i:s'),
                        'date_end' => $to->format('Y-m-d H:i:s'),
                        'seconds' => $seconds]);

        $readings = [];
        while($row = $stmt->fetch()){
            $from->add($interval);
            while($from->getTimestamp() < $row['rounded_timestamp']){
                $readings[] = ['timestamp' => $from->format('c'), 'value' => null];
                $from->add($interval);
            }

            $readings[] = ['timestamp' => $from->format('c'), 'value' => $row['average_value']];

        }

        return $readings;
    }

This will produce something like

         {  
            "timestamp":"2016-09-26T22:30:00+00:00",
            "value":null
         },
         {  
            "timestamp":"2016-09-26T23:00:00+00:00",
            "value":null
         },
         {  
            "timestamp":"2016-09-26T23:30:00+00:00",
            "value":null
         },
         {  
            "timestamp":"2016-09-27T00:00:00+00:00",
            "value":null
         },
         {  
            "timestamp":"2016-09-27T00:30:00+00:00",
            "value":null
         },
         {  
            "timestamp":"2016-09-27T01:00:00+00:00",
            "value":"-1.250000000000"
         },
         {  
            "timestamp":"2016-09-27T01:30:00+00:00",
            "value":"-3.511090456890"
         },
         {  
            "timestamp":"2016-09-27T02:00:00+00:00",
            "value":"-3.045483744205"
         },
         {  
            "timestamp":"2016-09-27T02:30:00+00:00",
            "value":"-3.534912718204"
         },
         {  
            "timestamp":"2016-09-27T03:00:00+00:00",
            "value":"4.875298964425"
         }

It's useful for 'pinning' the readings to a date/time so they can be plotted against other readings even if they aren't specifically at the same time.