0

Hello i am going to get data between 2 dates start to last date using monogdb. and i am getting success for get data between 2 dates but here some issue with get data i am going remove time and then compare only date then current date day data is not come here so any idea where is my mistake then please let me know.here below this is my code.

here this is my documents =>

        {
         "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
          "Action" : "Comment",
          "datetime" : 1509084155000 // 27th oct 2017 convert date just for info here write
     },
     {
           "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
           "Action" : "Comment",
           "datetime" : 1509084155000  // 27th oct 2017 convert date just for info here write
      },
      {
          "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
          "InId": ObjectId("595e3b2033961713940442cd")
          "Action" : "Comment",
          "datetime" : 1509084155000 // 27th oct 2017 convert date just for info here write
      },
      {
         "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
         "Action" : "Comment",
         "datetime" : 1509170555000 // 28th oct 2017 convert date just for info here write
    },
    {
           "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
           "Action" : "Comment",
           "datetime" : 1509170555000 // 28th oct 2017 convert date just for info here write
    },
    {
            "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
          "InId": ObjectId("595e3b2033961713940442cd")
            "Action" : "Comment",
            "datetime" : 1509343355000 // 30th oct 2017 convert date just for info here write
    }
    {
           "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
           "Action" : "Comment",
           "datetime" : 1509343355000  // 30th oct 2017 convert date just for info here write
     },
     {
            "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
            "Action" : "like",
            "datetime" : 1509429755000  // 31th oct 2017 convert date just for info here write
    },
     {
            "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
            "Action" : "like",
            "datetime" : 1509516155000 // 1th Nov 2017 convert date just for info here write
     },
     {
            "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
          "InId": ObjectId("595e3b2033961713940442cd")
            "Action" : "like",
            "datetime" : 1509611470000// 2th Nov 2017 convert date just for info here write
     }

my current o/p =>

  { _id: '27-10-2017', CommentCount: 3 ,Likecount:0},
  { _id: '28-10-2017', CommentCount: 2 ,Likecount:0},
  { _id: '30-10-2017', CommentCount: 2,Likecount:0 },
  { _id: '31-10-2017', CommentCount: 0,Likecount:1 },
  { _id: '01-11-2017', CommentCount: 0 ,Likecount:1},

my excepted o/p =>

  { _id: '27-10-2017', CommentCount: 3 ,Likecount:0},
  { _id: '28-10-2017', CommentCount: 2 ,Likecount:0},
  { _id: '30-10-2017', CommentCount: 2,Likecount:0 },
  { _id: '31-10-2017', CommentCount: 0,Likecount:1 },
  { _id: '01-11-2017', CommentCount: 0 ,Likecount:1},
  { _id: '02-11-2017', CommentCount: 0 ,Likecount:1},

This is my query =>

     function removetime(unx) {
        var ld = new Date(parseInt(unx))
        return ld.setUTCHours(0, 0, 0, 0);
       }
           ActivityHistory.aggregate([
              {
                  "$match": {
                      "InId": ObjectId("595e3b2033961713940442cd"),
                      "datetime": {
                          "$lte": removetime("1509602915994"), "$gte": removetime("1509084515994") // removetime funcation used for remove time 
                      }
                  }
              },
                {
                    "$project": {
                        datetime: 1,
                        ProComment: {
                            $cond: [{ $eq: ["$Action", "Comment"] }, 1, 0]
                        },
                        ProLike: {
                            $cond: [{ $eq: ["$Action", "like"] }, 1, 0]
                        }
                    }
                },
          {
              "$group": {
                  "_id": {
                      "$dateToString": {
                          "format": "%d-%m-%Y",
                          "date": {
                              "$add": [new Date(0), "$datetime"]
                          }
                      }
                  },
                  "CommentCount": { $sum: "$ProComment" },
                  "Likecount": { $sum: "$ProLike" }                     
              },
          },
          { '$sort': { '_id': 1 } } // here i have used sort but it's not work
            ]).exec(function (err, data) {
                if (err) {
                    console.log(err);
                }
                else {
                    console.log("Final=>", data);
                    res.send(data);
                }
            });
Edit
  • 385
  • 4
  • 24
  • Because you sorted by `d-m-Y` which is **NOT** lexically ordered. `Y-m-d` on the other hand **IS** lexically ordered. Casting to strings is "hacky". There are far better ways of "rounding" a date. i.e [Group result by 15 minutes time interval in MongoDb](https://stackoverflow.com/a/26814496/2313887) uses a "math" approach, and you can still return a BSON Date. Just "rounded" to the current day or desired interval. – Neil Lunn Nov 02 '17 at 06:21
  • How is that not a "hint"? If not actually "specific instruction"? I told you what was wrong and how to fix at, and also pointed you to what you should be doing instead. – Neil Lunn Nov 02 '17 at 06:26

0 Answers0