mongodb date aggregation operators timezone adjustments with php

Question

I'm trying to adjust the timezone with date aggregation operators. I need to make -7 hours adjustment on the $signs.timestamp field.

This is my code:

function statsSignatures() {
    $cursor = $this->db->collection->users->aggregate(
        array('$unwind' => '$signs'),
        array('$project'=>array(
            'signs'=>'$signs',
            'y'=>array('$year'=>'$signs.timestamp'),
            'm'=>array('$month'=>'$signs.timestamp'),
            'd'=>array('$dayOfMonth'=>'$signs.timestamp'),
            'h'=>array('$hour'=>'$signs.timestamp')
        )),
        array('$group'=>array(
            '_id'=>array('year'=>'$y','month'=>'$m','day'=>'$d','hour'=>'$h'),
            'total'=>array('$sum'=>1)
        )),
        array('$sort'=>array(
            '_id.year'=>1,
            '_id.month'=>1,
            '_id.day'=>1,
            '_id.hour'=>1
        ))
    );
    return $cursor['result'];
}

I'm using MongoDB version 2.6.3.

Thank you a lot !

Answer 1

You can use $project with $subtract operator to make a -7 hour adjustment to a Date field:

{
    $project : { 
        ts : { $subtract : [ "$signs.timestamp", 25200000 ] }
    }
}

// 25200000 == 1000 milis x 60 sec x 60 mins x 7 h 

The projected field ts is a Date that's offset by -7 hours.

Edit

This is the correct PHP syntax when using $subtract.

array(
    '$project' => array( 
        'ts' => array('$subtract' => array('$signs.timestamp', 25200000))
    )
)

Subtract accepts an array of values, not a key=>value pair.