MongoDB equivalent to NOW() + INTERVAL

Question

I'm currently working on converting our PHP backend from MySQL to MongoDB.

We are often using something like this in MySQL:

UPDATE table_1 SET completion_time = NOW() + INTERVAL 90 MINUTE WHERE id = 1;

How would I do this in MongoDB? Do I need to use 2 queries? First query to set completion_time with $currentDate and the 2nd query to increment it? I've read that $inc doesn't work on Dates in MongoDB, tho ...

not a duplicate, but similar: http://stackoverflow.com/q/7675549/1090562 — Salvador Dali, Mar 23 '15 at 09:59
that doesn't really help. They either use a string or $currentDate. I need to add something to the currentDate at the same time. Also creating the date in PHP first is a bad idea since you cannot guarentee that all php servers have the exact same date. — DeXter, Mar 23 '15 at 10:03
@DeXter Are you using two differnt timezones between the DB and the App, maybe the DB could be in multiple timezones as well? — Sammaye, Mar 23 '15 at 11:27
You can't use `$currentDate` with an offset to set the time server-side, but you can timestamp the doc with now + an offset client-side and send that datetime value to be stored in the server. I won't try to write code for it since I'm not familiar with PHP. — wdberkeley, Mar 23 '15 at 16:25

score 2 · Answer 1 · answered Mar 23 '15 at 10:14

You could try creating a date variable that holds the current date + 90 minutes later which you can then use to set the completion_time field with in your update:

var ninetyMinutesLater = new Date();
ninetyMinutesLater.setMinutes(ninetyMinutesLater.getMinutes() + 90);
db.table_1.update(
   { "_id": 1 },
   {     
     "$set": {
        "completion_time": ninetyMinutesLater 
     }
   }    
);

score 0 · Answer 2 · answered Mar 23 '15 at 11:22

I see you have have tagged this with mongodb-php so I assume you are using PHP. MongoDB does have a $currentDate operator but currently there is no way to actually set an offset to that date.

For example I tried @diwakar's answer on 3.0:

> db.table_1.update({"id":1 },{completion_time: { $add: [ "$currentDate", 90 ] }})
2015-03-23T11:22:05.497+0000 E QUERY    Error: field names cannot start with $ [$add]
    at Error (<anonymous>)
    at DBCollection._validateForStorage (src/mongo/shell/collection.js:160:19)
    at DBCollection._validateForStorage (src/mongo/shell/collection.js:164:18)
    at DBCollection._validateUpdateDoc (src/mongo/shell/collection.js:387:14)
    at Object.findOperations.updateOne (src/mongo/shell/bulk_api.js:675:20)
    at DBCollection.update (src/mongo/shell/collection.js:454:22)
    at (shell):1:12 at src/mongo/shell/collection.js:160

So currently this needs to be done client side like so:

$mongo->collection->insert(['completion_time' => new MongoDate(time() + (60*90))])

It seems that is the only way.

score 0 · Answer 3 · answered Oct 05 '22 at 18:14

Starting with MongoDB v5.0+, you can use $dateAdd with $$NOW.

db.collection.update({
  _id: 1
},
[
  {
    $set: {
      completion_time: {
        "$dateAdd": {
          "startDate": "$$NOW",
          "unit": "minute",
          "amount": 90
        }
      }
    }
  }
])

Here is the Mongo Playground for your reference.

score -1 · Answer 4 · answered Oct 04 '22 at 07:21

-1

use

db.collection.UpdateOne({ _id:id},{
     $set:{
      "completion_time": $add:[ $currentDate , (60*90)]
      }
})

answered Oct 04 '22 at 07:21

harsh dixit

1
1

This looks a lot like [Diwakar upadhyay's 2015 answer](https://stackoverflow.com/a/29207511/3789665): without at least a version where you found it to work, why should it [fare better](https://stackoverflow.com/a/29208996/3789665)? – greybeard Oct 09 '22 at 07:01

score -2 · Answer 5 · edited Mar 23 '15 at 10:24

-2

Please try this definitely work,

db.table_1.update({"id":1 },{completion_time: { $add: [ "$date", 90 ] }})

edited Mar 23 '15 at 10:24

Markus W Mahlberg

19,711
6
65
89

answered Mar 23 '15 at 10:08

Diwakar upadhyay

434
5
14

1

What is that supposed to do? Where does $date come from (the only date spec field that MongoDB currently has is http://docs.mongodb.org/manual/reference/operator/update/currentDate/ )? Also are you sure that `$add` can actually be used in a update statement? – Sammaye Mar 23 '15 at 11:19
Did you test this code? This results in an `uncaught exception: field names cannot start with $ [$add]` error. – chridam Mar 23 '15 at 11:22

MongoDB equivalent to NOW() + INTERVAL

5 Answers5