mongodb query based on date + x days from the documents

Question

consider this mongo collection with following documents and props.

{sku: '3344', frequency: 30, lastProccessedDate: 2021-01-07 15:18:07.576Z},
{sku: '2233', frequency: 30, lastProccessedDate: 2021-02-16 15:18:07.576Z},
{sku: '1122', frequency: 30, lastProccessedDate: 2021-04-13 15:18:07.576Z}

I want to query and get all the documents with (lastProcessedDate + frequency (days)) <= current date.

Essentially in SQL world this is possible to do it, but I can't figure it out to do it in mongo or even if it is possible to do it.

In SQL this would be something like SELECT * FROM table WHERE DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(lastProcessedDate), INTERVAL frequency DAY), '%Y-%m-%d') <= CURDATE()

If it is not possible I know I can store the calculated date in the document and just query based on that but you know I want to know if it is possible to do it.

Thank you all!

Igor Amelin · Accepted Answer · 2021-04-15 19:57:39Z

1

Unfortunately, I can't give you a solution for mongoose. But here is the Mongo query that returns the result you want:

db.getCollection("yourCollection").aggregate([
{
   $project: {
       _id: 1,
       sku: 1,
       frequency: 1,
       lastProccessedDate: 1,
       shiftedDate: {
           $add: [
               "$lastProccessedDate", { $multiply: [ 24 * 60 * 60 * 1000, "$frequency" ] }
           ]
       }
   } 
}, {
    $match: {
        shiftedDate: { $lte: new Date() } 
    }
}, {
    $project: {
        _id: 1,
       sku: 1,
       frequency: 1,
       lastProccessedDate: 1,
    }
}
])

First, we transform documents to new form that contains the same fields plus a new temporary field - a "shifted" date that is defined as lastProccessedDate + frequency (days) (pay attention that we actually add milliseconds, so there's 24 * 60 * 60 * 1000 in the query). Then we select only that documents which shiftedDate is less than (or equals) current timestamp (new Date() returns current timestamp). Finally, we transform the filtered documents to original form, without the temporary field we used to filter the documents previously.

Perhaps there's a better solution to get documents you want, but this one can resolve your problem too.

answered Apr 15, 2021 at 19:57

Igor Amelin

3851 gold badge2 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wernfried Domscheit Over a year ago

Instead of $project you can use $set. Then you save all the xx: 1

Collectives™ on Stack Overflow

mongodb query based on date + x days from the documents

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related