Calculations with MongoDB dates

programming
1

I need to create a View in MongoDB which only shows documents created within the last year. Using fixed dates, that is easy. I can simply:

db.comments.find({
    dtCreated: {
        $gte: '2019-10-16 00:00:00'
    }
})

But how do I make it so it is always one year ago from the current date? For example:

db.comments.find({
    dtCreated: {
        $gte: ISODate() - 365
    }
})

Thanks in advance for any suggestions.

2

I’ve managed to get the right formula. Here’s what I did:

$gte: new Date((new Date()).getTime() - (366 * 24 * 60 * 60 * 1000))

Unfortunately, that does not get inserted in the view structure as a formula that is calculated every time the view is accessed. Instead, it gets calculated upon creation and the actual result of the formula is inserted in the view structure. In other words, the view is always returning data from that date, which was calculated when the view was created.

Any suggestions?

3

Unfortunately, I haven’t used mongoDB in years to be able to help. These forums are typically are more front-end focused, so a database-related question doesn’t have the right set of eyes on it :frowning:

4

It is OK. It is a great community and I am glad to be part of it. Someone kindly pointed me to the right direction, involving the use of the $$NOW system variable. For completion, in case someone is interested, here is the code that solved the problem:

[ { 
  $match: { 
      $expr: { 
          $gte: [ "$created", { $subtract: [ "$NOW", 31536000000 ] } ] 
      } 
  } 
} ]

The number ‘31536000000’ being simply the result of 365 days in milliseconds (365 * 24 * 60 * 60 * 1000). It worked like a charm. Many thanks and all credit to Prasad_.

1 Like