0

I have a collection where a single document looks like this:

{
  "_id" : "123456789",
   "created_timestamp" : "1522950461031"
}

As you can see, the created_timestamp date is stored as the epoch value as a String. (unfortunately can't change that now)

I need to write a query that will calculate the "time elapsed" i.e, current time - created_time for all of the documents at any given point in time.

This is what I tried so far:

db.getCollection('testEntity').aggregate([
{$match : {"_id" : "123456789"}},
{$project: {_id : 1, created_timestamp: 1 ,
    dateDifference: { $subtract: [ new Date() , new  Date("$created_timestamp")]}}}])

But it does not compute the difference correctly. I think the field is not getting detected correctly within the $subtract. Any suggestions to rewrite it correctly?

Bajal
  • 5,487
  • 3
  • 20
  • 25
  • `dateDifference: { $subtract: [ new Date() , "$created_timestamp" ] }` will subtract the two BSON dates and return the milliseconds time difference between them. There's even further demonstration on going back to BSON Date of getting milliseconds since epoch on [Group Result by 15 minute Time Interval](https://stackoverflow.com/a/26814496/2313887). But your field here is a "string" and not numeric. Right now MongoDB cannot do that, and there is information on that there as well. – Neil Lunn Jun 01 '18 at 21:25
  • Thanks, I will look into converting my field to numeric. – Bajal Jun 02 '18 at 15:22

1 Answers1

1

MongoDb doesn't provides type changes. And in your case timestamp should be in number for your conditions to be applied. So, i suggest that you explicitly convert the data in your db by some programming code using update query.

xfankit
  • 36
  • 3