MongoDB query based on date plus time

Question

i've to run a query like this (sql) in MongoDb 4:

SELECT * FROM log WHERE DATE_ADD(created_at, INTERVAL 2 HOUR) < NOW()

Basically, I want to find all the documents, in the PENDING state, whose creation date PLUS TWO HOURS is less than now .. Let me explain: I want to find all the documents in the PENDING state that have been in PENDING for more than two hours. I feel stupid, but I am failing to do this with MongoDb. I also created a playground:

https://mongoplayground.net/p/4bifqiX2KMJ

Can you help me?

turivishal · Accepted Answer · 2020-09-08 10:47:09Z

1

You can add hours in ISO date using $add, convert string date to ISO date using dateFromString,

let date = new Date();

db.collection.find({
  status: "pending",
  $expr: {
    $lt: [
      {
        $add: [
          // convert string date to ISOdate, if its already then use only "$inserted_at"
          { $dateFromString: { dateString: "$inserted_at" } },
          // add milliseconds
          7200000 // (60*60*2000)
        ]
      },
      date
    ]
  }
})

Playground

Or subtract from current date and then compare the condition,

let date = new Date();
date = new Date(date.getHours()-2); //subtract 2 hours

db.collection.find({
  status: "pending",
  $expr: {
    $lt: [
      { $dateFromString: { dateString: "$inserted_at" } },
      date
    ]
  }
})

Playground

edited Sep 8, 2020 at 10:47

answered Sep 8, 2020 at 10:31

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

2 Comments

Steve Aged Over a year ago

thank you for your reply, but I am not understanding: the two hours must be added to the date of the document itself, not to the current date or to a preset date. I have to find, as written, all the documents in "Pending" status where the insert_at plus two hours is in the past ..

turivishal Over a year ago

Have you check and experiment with first example? that is similar to your select query. I have to find, as written but your mysql select query will find written?

Collectives™ on Stack Overflow

MongoDB query based on date plus time

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related