Mongodb get average value over period of time per day in PHP

Question

I am playing around with MongoDB and although I've seen posts on the internet about problems similar to mine, but I can't found out how to do it.

I have a quite some documents in my collection that hold sensor measurement data like this:

{
      'timestamp' => new MongoDate(1503119239, 0),
      'sensor_id' => new MongoInt64(4),
      'value' => 12345
}

With of course the timestamp, sensor_id and value different every record.

Using PHP I want to get the average values PER sensor PER day/month/hour. For example I have tried getting the average per day by using

$pipeline = array(
    array(
        '$group' => array(
            '_id'      => ['sensor_id'=> '$sensor', '$dayOfYear'=>'$timestamp'],
            'avgValue' => array( '$avg' => '$value' ),
        ),
    ),
);

$out = $logdataCollection->aggregate( $pipeline );

I'd expect it to give me an output of, for example

[0] => ['sensor_id' => '1', 'dayOfYear'=>12, 'avgValue'=>1.123],
[1] => ['sensor_id' => '1', 'dayOfYear'=>13, 'avgValue'=>0.15],
[2] => ['sensor_id' => '2', 'dayOfYear'=>12, 'avgValue'=>1.123],
[3] => ['sensor_id' => '3', 'dayOfYear'=>15, 'avgValue'=>1.123],

etc.

This example shows the average by day (over the period of a year), but I want to be able to this per hour as well over the period of a month:

[0] => ['sensor_id' => '1', 'date'=>2015-01-01, 'avgValue'=>1.123],
[1] => ['sensor_id' => '1', 'date'=>2015-01-02, 'avgValue'=>0.15],
[2] => ['sensor_id' => '2', 'date'=>2015-01-01, 'avgValue'=>1.123],
[3] => ['sensor_id' => '3', 'date'=>2015-01-02, 'avgValue'=>1.123],

etc.

At first my timestamps where just numeric but I thought inserting them as MongoDateTime would perhaps make a difference in this. It didn't.

I have read the docs about Aggregration framework, SO and the PHP website, but I just can not find out how to do it. Please help!

Answer 1

Finally I found out how to get the results for my examples. I am groupping them on a formatted date as follows:

 $pipeline = array(
    array(
        '$group' => array(
            '_id'      => ['foundDate'=>['$dateToString'=>['format'=>'%Y-%m-%d', 'date'=>'$timestamp']], 'sensor'=>'$sensor'],
            'avgValue' => array( '$avg' => '$value' ),
        ),
    ),
);

In which i can set the date format to anything, the query will group on that. For example the mentioned code will output:

[0]=>
    array(2) {
      ["_id"]=>
      array(2) {
        ["foundDate"]=>
        string(10) "2016-03-23"
        ["sensor"]=>
        int(2)
      }
      ["avgValue"]=>
      float(526.622580645)
    }
    [1]=>
    array(2) {
      ["_id"]=>
      array(2) {
        ["foundDate"]=>
        string(10) "2016-03-23"
        ["sensor"]=>
        int(3)
      }
      ["avgValue"]=>
      float(480.773846154)
    }
   ...