Mongo find timestamps in a array, between two dates

Question

I have documents with a array property timestamps as seen in the document below. I wish to find all documents with timestamps between two dates. My find query looks as follow:

    $subset = $db->col->find(
            array( 
                'timestamps'=> array(
                    '$elemMatch'=> array(
                        '$gte'=> new MongoDate(strtotime('2016-05-25 13:00:00')),
                        '$lte'=> new MongoDate(strtotime('2016-05-25 14:00:00'))
                    )
                )
            )
    );

But it does not return the expected documents. I tried to Google and search StackOverflow for answer but I cannot find anything explaining how to search for timestamps in a array, between two dates.

EDIT:

It seems the problem lies with PHP because the same Python query works:

import datetime
f = '%Y-%m-%d %H:%M:%S'
c = db.col.find({
        'timestamps': {
            '$elemMatch': {
                '$gte': datetime.datetime.strptime('2016-05-25 13:00:00', f),
                '$lte': datetime.datetime.strptime('2016-05-25 14:00:00', f)
            }
        }
    })

Any help would be appreciated.

{
    "month": {
        "$date": 1464134400000
    }, 
    "timestamps": [
        {
            "$date": 1464181803000
        }, 
        {
            "$date": 1464182103000
        }, 
        {
            "$date": 1464182403000
        }, 
        {
            "$date": 1464183003000
        }, 
        {
            "$date": 1464183603000
        }, 
        {
            "$date": 1464184203000
        }, 
        {
            "$date": 1464184803000
        }, 
        {
            "$date": 1464185403000
        }, 
        {
            "$date": 1464186003000
        }
    ], 
    "status_history": [
        1, 
        1, 
        1, 
        0, 
        1, 
        1, 
        1, 
        0, 
        1
    ], 
    "_id": 12345
}

I had a look at find in array between dates in mongo and Get data between two dates mongo.

score 0 · Answer 1 · answered May 25 '16 at 13:43

It was a timezone issue. I created a DateTime and set the timezone to Etc/GMT and the expected data returned correctly.

$gte = new MongoDate (new DateTime( '2016-05-25 13:00:00', new DateTimeZone( 'Etc/GMT' )));
$lte = new MongoDate (new DateTime( '2016-05-25 14:00:00', new DateTimeZone( 'Etc/GMT' )));

Mongo find timestamps in a array, between two dates

1 Answers1