Adding/Subtracting days to ISODate in MongoDB Shell

Question

I have a query where I need to get events that are one day before or after from a specific date. I need to add or subtract one day to that ISODate variable. Here is my query :

db.event.find().forEach( function (x) {

  print("x : " + x.EventID + ", " + x.ISODate); 
  db.events.find( {
   "$or" : [{
       "StartDate" : { "$gte" : x.ISODate } // Here i need to subtract one day
       }, {
           "EndDate": { "$lt" : x.ISODate} // Here i need to add one day
           }]
}).forEach(function(otherDay) {
        print("x.EventID : " + x.EventID + ", other.Date : " + otherDay.StartDate + " - " + otherDay.EndDate);
      });

});

How can i add or subtract days to an ISODate variable in mongodb shell?

Help me to understand your query here: you want to look for events which have a `StartDate` greater than or equal to a particular date, say `ISODate("2015-06-21T23:59:59.999Z")` OR an `EndDate` less than `ISODate("2015-06-21T00:00:00.000Z")`? — chridam, Jun 23 '15 at 07:57
I want to get events that are between startdate and end date. I guess it was wrong in the question and i edited. I want to subtract from startdate and add to enddate. — cuneyttyler, Jun 23 '15 at 09:16

score 58 · Answer 1 · edited May 23 '17 at 11:47

58

This has been answered on Query to get last X minutes data with Mongodb

query = {
    timestamp: { // 18 minutes ago (from now)
        $gt: new Date(ISODate().getTime() - 1000 * 60 * 18)
    }
}

And in your case, for a number of days:

"StartDate" : { "$gte" : new Date(ISODate().getTime() - 1000 * 3600 * 24 * 3) }

or

"StartDate" : { "$gte" : new Date(ISODate().getTime() - 1000 * 86400 * 3) }

(here the 3 is your number of days)

edited May 23 '17 at 11:47

Community

1
1

answered Sep 16 '15 at 17:48

Constantin Guay

1,604
2
15
26

can you explain about all those numbers? what is 1000 and 86400. – Racer SQL Aug 14 '19 at 18:16
2

Hi, yes, 86400 is 3600 seconds (1 hour) by 24 (hours) so it's the number of seconds in one day. 1000 is to have it in milliseconds to match JS Date format – Constantin Guay Aug 15 '19 at 09:50
2

Does this handle daylight changes etc correctly? If it's 1:59 am and today (say day 22 of month X) we skip from 2:00am to 3:00am will `new Date(ISODate().getTime() + 1000 * 86400 * 1)` give me a date in day 23 month X or will it skip to beginning of day 24 of month X? When we say "add one day" we don't necessarily mean add 24 hours, but increase the day number by one. In case of daylight saving changes that day can have more or less than 24 hours and so this answers seems completely broken. – Giacomo Alzetta Oct 16 '19 at 13:31
@Racer SQL we work with milliseconds, so to get a second, you have to multiply it by 1000 – Artem Fedotov Jan 20 '20 at 07:01
Your answer gives `3 days minus now`. Where as the OP wanted to add or subtract days from ISODate, so he can query between start and end date. – Akshay Hazari Feb 24 '20 at 08:22
for me getTime() and new date is not working setting date to 1970, but below answer works. https://stackoverflow.com/a/60039977/7816309 – Kanish Aug 09 '22 at 12:57

score 34 · Answer 2 · edited Oct 04 '22 at 09:02

Not an exact answer but related.

I needed to increment a date field for all items in my MongoDB collection in place, relative to the date value that was stored.

The query below will add 1 day to myDateField in myCollection.

db.myCollection.find().snapshot().forEach(
    function (elem) {

        const dateDiffInMs = 1 * 24 * 60 * 60000

        db.myCollection.update(
            {
                _id: elem._id
            },
            {
                $set: {
                    myDateField: new Date(elem.myDateField.getTime() + dateDiffInMs)
                }
            }
        );
    }
);

1 day = 1 * 24 * 60 * 60000 = 1 day x 24 hours x 60 minutes x 60 seconds x 1000 milliseconds

Use minus value for dateDiffInMs if you need to subtract / go back in time.

score 5 · Answer 3 · answered Feb 24 '20 at 10:50

I feel your question was misunderstood.

I hope you needed to subtract a day and add a day in your query , which you could do like so: {$subtract : [x.ISODate,(1 * 24 * 60 * 60 * 1000)]}

db.event.find().forEach( function (x) {

  print("x : " + x.EventID + ", " + x.ISODate); 
  db.events.find( {
   "$or" : [{
       "StartDate" : { "$gte" : {$subtract : [x.ISODate,(1 * 24 * 60 * 60 * 1000)]} } // Here i need to subtract one day
       }, {
           "EndDate": { "$lt" : {$subtract : [x.ISODate,(-1 * 24 * 60 * 60 * 1000)]}} // Here i need to add one day
           }]
}).forEach(function(otherDay) {
        print("x.EventID : " + x.EventID + ", other.Date : " + otherDay.StartDate + " - " + otherDay.EndDate);
      });

});

score 2 · Answer 4 · answered Sep 21 '22 at 10:06

From Mongo 5 you can use $dateSubtract:

{
 $dateSubtract: {
  startDate: "$$NOW",
  unit: "day",
  amount: 1
 }
}

That will be create yesterday's ISODate(). For older versions of mongo you can use this:

new Date(ISODate().setDate(ISODate().getDate() - 1))

Adding/Subtracting days to ISODate in MongoDB Shell

4 Answers4

Linked