Date day/minute in mongodb queries

Question

I have time series data stored in a mongodb database, where one of the fields is an ISODate object. I'm trying to retrieve all items for which the ISODate object has a zero value for minutes and seconds. That is, all the objects that have a timestamp at a round hour.

Is there any way to do that, or do I need to create separate fields for hour, min, second, and query for them directly by doing, e.g., find({"minute":0, "second":0})?

Thanks!

score 6 · Accepted Answer · answered Mar 26 '13 at 14:03

6

You could do this as @Devesh says or if it fits better you could use the aggregation framework:

db.col.aggregate([
   {$project: {_id:1, date: {mins: {$minute: '$dateField'}, secs: {$second: '$dateField'}}}},
   {$match: {mins: 0, secs: 0}}
]);

Like so.

answered Mar 26 '13 at 14:03

Sammaye

43,242
7
104
146

Thanks. That's actually what i was looking for, but Devesh's answer may lead to better performance in my case since I'll be running a huge number of queries with these constraints. – user1094206 Mar 26 '13 at 14:07
@user1094206 indeed, pre-aggregating to another field, selectively and carefully will produce faster and more scalable results, however I would not use a date time column since there is no such thing in MongoDB only a date column, instead you would house an object whose parts form a date, i.e.: `date: {hours: 1, mins: 0, secs: 0}` – Sammaye Mar 26 '13 at 14:11

score 3 · Answer 2 · answered Aug 10 '18 at 15:20

Use the $expr operator along with the date aggregate operators $minute and $second in your find query as:

db.collection.find({
    '$expr': {
        '$and': [
            { '$eq': [ { '$minute': '$dateField' }, 0 ] },
            { '$eq': [ { '$second': '$dateField' }, 0 ] },
        ]
    }
})

score 1 · Answer 3 · answered Mar 26 '13 at 13:51

1

Can you have one more column added in the collection only containing the datetime without minutes and seconds . It will make your query faster and easy to use. It will be datetime column with no minutes and seconds parts

answered Mar 26 '13 at 13:51

Devesh

4,500
1
17
28

That seems like a good idea. I will go with that unless there is a way to filter directly based on min/sec. – user1094206 Mar 26 '13 at 14:01

Date day/minute in mongodb queries

3 Answers3