CODEWITHSUNDEEP
mongodbtimestampdays
momo
momo

Reputation: 23

Mongodb how to find how much days ago from a timestamp field

I was trying to find the number of days ago using the timestamp but i dont know how to do that ?

{
    "_id" : ObjectId("5504cc9ddd5af617caae30b3"),
    "session_id" : 1,
    "Timestamp" : "2014-04-07T10:51:09.277Z",
    "Item_ID" : 214536502,
    "Category" : 0
}

How I can calculate the number of days ago using the field "Timestamp" ?

Upvotes: 1

Views: 2666

Answers (1)

Anzel
Anzel

Reputation: 20553

You may use aggregate, $project with new Date() on the Timestamp field, then do the calculation, something like this:

pipe = {
    "$project" : {
        "_id" : 1,
        "daySince" : {
            "$divide" : [
                {
                    "$subtract" : [
                        new Date(),
                        new Date("$Timestamp")
                    ]
                },
                86400000
            ]
        }
    }
}

To calculate:

db.collection.aggregate(pipeline=pipe)

Since Timestamp isn't a ISODate object, you just need to convert it to one, then subtract to current date, and divide the result by 60*60*24*1000, then it will be the number of days since today.

You can also change the new Date() to what you need to be compared.

Updated:

Since I believe the Timestamp format might be malformed, alternatively you may use mapReduce functions to calculate this:

// in your mongo shell using the db

var mapTimestamp = function() { 
    daySince = parseInt(new Date() - new Date(this.Timestamp) / 86400000);
    emit(this._id, daySince); 
}
// since you map reduce only on one field, there's really no need for this
var reduceTimestamp = function (key, value) { return value; }

db.collection.mapReduce(mapTimestamp, reduceTimestamp, {out: "sample"})

To show the results:

db.sample.find()

Upvotes: 4

Related Questions