6

In my mongodb collection, I have a time_stamp="2013-06-30 23:58:37 928". I need to use "$match" with only the date, like time_stamp="2013-06-30". So how can I get the substring like that ?

Previously I've tried with $substr, but it shows an error "errmsg" : "exception: invalid operator: $substr"

Shashika
  • 1,606
  • 6
  • 28
  • 47
  • This is so common that there's a cookbook page with the pattern: http://cookbook.mongodb.org/patterns/date_range/ it works with strings as shown in one of the answers. – WiredPrairie Nov 19 '13 at 12:11
  • Also, you marked as an answer the option that won't use an index. It will need to do a full collection scan. – WiredPrairie Nov 19 '13 at 12:13

2 Answers2

7

I think you are trying to make query using aggregation framework since you tried $match & $substr operators. I have created a simple example to show how you can use $substr to achive result you wanted on aggregation framework.

I have inserted following data into the MongoDB.

{ "_id" : ObjectId("528b343881d4fe2cfe0b1b25"), "time_stamp" : "2013-06-30 23:58:37 928" }
{ "_id" : ObjectId("528b343b81d4fe2cfe0b1b26"), "time_stamp" : "2013-06-30 23:58:37 928" }
{ "_id" : ObjectId("528b344c81d4fe2cfe0b1b27"), "time_stamp" : "2013-06-30 12:58:37 928" }
{ "_id" : ObjectId("528b344f81d4fe2cfe0b1b28"), "time_stamp" : "2013-06-30 12:58:23 928" }
{ "_id" : ObjectId("528b345381d4fe2cfe0b1b29"), "time_stamp" : "2013-06-31 12:58:23 928" }
{ "_id" : ObjectId("528b345981d4fe2cfe0b1b2a"), "time_stamp" : "2013-07-31 12:58:23 933" }

I wrote following code to group by date by using $substr operator.

db.myObject.aggregate(
{$project : {new_time_stamp : {$substr : ["$time_stamp",0, 10]}}},
{$group:{_id:"$new_time_stamp", "count": {$sum:1}}}
);
Parvin Gasimzade
  • 25,180
  • 8
  • 56
  • 83
3

$substr operation failed because you there is no such operation for find. You can only use it in aggregation framework. You mentioned that you have a timestamp. In such a case you need to query by time interval.

{time_stamp: {
  $gte: ISODate("2013-06-30T00:00:00Z"),
  $lt: ISODate("2013-06-31T00:00:00Z")
}}

I hope that this month has 31 days. If it is a string ( which is really bad because dates should be stored as dates) you have to use a regex.

Salvador Dali
  • 214,103
  • 147
  • 703
  • 753