Mongodb aggregation by day based on unix timestamp

Question

I have googled alot, but not found any helpful solution... I want to find total number of daily users. I have a collection named session_log having documents like following

{
    "_id" : ObjectId("52c690955d3cdd831504ce30"),
    "SORTID" : NumberLong(1388744853),
    "PLAYERID" : 3,
    "LASTLOGIN" : NumberLong(1388744461),
    "ISLOGIN" : 1,
    "LOGOUT" : NumberLong(1388744853)
}

I want to aggregate from LASTLOGIN...

This is my query:

db.session_log.aggregate(
    { $group : {
        _id: {
            LASTLOGIN : "$LASTLOGIN"
        },
        count: { $sum: 1 }
    }}
);

But it is aggregating by each login time, not by each day. Any help would be appreciated

Answer 1

MongoDB 4.0 and newer

Use $toDate

db.session_log.aggregate([
    { "$group": {
        "_id": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": {
                    "$toDate": { 
                        "$multiply": [1000, "$LASTLOGIN"]
                    }
                }
            }
        },
        "count": { "$sum": 1 }
    } }
])

or $convert

db.session_log.aggregate([
    { "$group": {
        "_id": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": {
                    "$convert": { 
                        "input":  { 
                            "$multiply": [1000, "$LASTLOGIN"] 
                        }, 
                        "to": "date"
                    }
                }
            }
        },
        "count": { "$sum": 1 }
    } }
])

---

MongoDB >= 3.0 and < 4.0:

db.session_log.aggregate([
    { "$group": {
        "_id": {
            "$dateToString": {
                "format": "%Y-%m-%d",
                "date": {
                    "$add": [
                        new Date(0), 
                        { "$multiply": [1000, "$LASTLOGIN"] }
                    ]
                }
            }
        },
        "count": { "$sum": 1 }
    } }
])

---

You would need to convert the LASTLOGIN field to a millisecond timestamp through multiplying the value by 1000

{ "$multiply": [1000, "$LASTLOGIN"] }

, then convert to a date

"$add": [
    new Date(0),
    { "$multiply": [1000, "$LASTLOGIN"] }
]

and this can be done in the $project pipeline by adding your milliseconds time to a zero-milliseconds Date(0) object, then extract $year, $month, $dayOfMonth parts from the converted date which you can then use in your $group pipeline to group the documents by the day.

You should thus change your aggregation pipeline to this:

var project = {
    "$project":{ 
        "_id": 0,
        "y": {
            "$year": {
                "$add": [
                    new Date(0),
                    { "$multiply": [1000, "$LASTLOGIN"] }
                ]
            }
        },
        "m": {
            "$month": {
                "$add": [
                    new Date(0),
                    { "$multiply": [1000, "$LASTLOGIN"] }
                ]
            }
        }, 
        "d": {
            "$dayOfMonth": {
                "$add": [
                    new Date(0),
                    { "$multiply": [1000, "$LASTLOGIN"] }
                ]
            }
        }
    } 
},
group = {   
    "$group": { 
        "_id": { 
            "year": "$y", 
            "month": "$m", 
            "day": "$d"
        },  
        "count" : { "$sum" : 1 }
    }
};

Running the aggregation pipeline:

db.session_log.aggregate([ project, group ])

would give the following results (based on the sample document):

{ "_id" : { "year" : 2014, "month" : 1, "day" : 3 }, "count" : 1 }

---

An improvement would be to run the above in a single pipeline as

var group = {   
    "$group": { 
        "_id": {    
            "year": {
                "$year": {
                    "$add": [
                        new Date(0),
                        { "$multiply": [1000, "$LASTLOGIN"] }
                    ]
                }
            },
            "mmonth": {
                "$month": {
                    "$add": [
                        new Date(0),
                        { "$multiply": [1000, "$LASTLOGIN"] }
                    ]
                }
            }, 
            "day": {
                "$dayOfMonth": {
                    "$add": [
                        new Date(0),
                        { "$multiply": [1000, "$LASTLOGIN"] }
                    ]
                }
            }
        },  
        "count" : { "$sum" : 1 }
    }
};

Running the aggregation pipeline:

db.session_log.aggregate([ group ])

---

Answer 2

First thing is your date is stored in timestamp so you need to first convert timestamp to ISODate using adding new Date(0) and multiply timestamp to 1000 then you will get the ISODate like this :

{"$add":[new Date(0),{"$multiply":[1000,"$LASTLOGIN"]}]} this convert to timestamp to ISODate.

Now using date aggregation you need to convert ISODate in required format using $concat and then group by final formatting date so aggregation query will be :

db.session_log.aggregate({
  $project: {
    date: {
      $concat: [{
        $substr: [{
          $year: {
            "$add": [new Date(0), {
              "$multiply": [1000, "$LASTLOGIN"]
            }]
          }
        }, 0, 4]
      }, "/", {
        $substr: [{
          $month: {
            "$add": [new Date(0), {
              "$multiply": [1000, "$LASTLOGIN"]
            }]
          }
        }, 0, 4]
      }, "/", {
        $substr: [{
          $dayOfMonth: {
            "$add": [new Date(0), {
              "$multiply": [1000, "$LASTLOGIN"]
            }]
          }
        }, 0, 4]
      }]
    }
  }
}, {
  "$group": {
    "_id": "$date",
    "count": {
      "$sum": 1
    }
  }
})

If you will used mongo version 3.0 and above then use dateToString operator to convert ISODate to predefined format, and aggregation query is :

db.session_log.aggregate({
  "$project": {
    "ISODate": {
      "$add": [new Date(0), {
        "$multiply": [1000, "$LASTLOGIN"]
      }]
    }
  }
}, {
  "$project": {
    "yearMonthDay": {
      "$dateToString": {
        "format": "%Y-%m-%d",
        "date": "$ISODate"
      }
    }
  }
}, {
  "$group": {
    "_id": "$yearMonthDay",
    "count": {
      "$sum": 1
    }
  }
})