CODEWITHSUNDEEP
mongodb
Jack A
Jack A

Reputation: 57

MongoDB shell error converting millisecs integer to microseconds and then to Date field

Want to pass an integer holding microseconds since Epoch, to MongoDB shell's (version ~ 4.2) Date function to obtain a date. I understand MongoDB Date can take as argument, integer of milliseconds not microseconds, so need to convert from micro to milliseconds first by dividing by 1000.

Purpose is to add a date field 'new_field5' to collection, sample format below, initialising it's value from an existing integer field in collection called 'ts', which has epoch time in microseconds.

Step 1: First tried converting a static value of microseconds to milliseconds in aggregration pipeline but all the dates are getting initialised as intial date of epoch.

db.mycollection.update({}, [ {"$set" :{"new_field5":  [new Date( [ {$round : [{ $divide: [ 1675750431775274 , 1000 ] },0]} ])] }} ], {multi:true})

Step 2: Would then amend query to take the milliseconds integer from field ts

db.mycollection.update({}, [ {"$set" :{"new_field5":  [new Date( [ {$round : [{ $divide: [ "$ts" , 1000 ] },0]} ])] }} ], {multi:true})

Below works: if I start with integer containing milliseconds with no division required, the Date field is correctly derived from it. eg;

db.mycollection.update({},  {"$set" :{"new_field5":  [new Date(   1675750431775) ]}}  , {multi:true})

Collection layout:

{
  "_id": "nnn-f26c7979-99c8-4b27-b8e6-8f57e27c0fb4_1675750435048498",
  "sessionId": "nnn-f26c7979-99c8-4b27-b8e6-8f57e27c0fb4",
  "orchId": "orch-c9cd830e-917c-4021-b8ff-cecf1d534c34",
  "cvId": "cv-f26c7979-99c8-4b27-b8e6-8f57e27c0fb4",
  "vName": "orch",
  "data": "{\"text\":\"/inform{\\\"_MobileDevice\\\": \\\"apple\\\"}\"}",
  "ts": {
    "$numberLong": "1675750435048498"
  },
  "new_field5": [
    {
      "$date": {
        "$numberLong": "0"
      }
    }
  ]
}

Upvotes: 0

Views: 52

Answers (2)

Joe
Joe

Reputation: 28366

The $round and $divide operators only have meaning on the server side, but the new Date() constructor will be executed on the client side, before sending the query to the server.

You should either use javascript functions to construct the date on the client side:

db.mycollection.update({}, 
  [ {"$set" :{"new_field5":  [new Date(Math.floor(1675750431775274/1000))]}}], {multi:true})

or use the $toDate operator to construct the date on the server side:

db.mycollection.update({},
[{"$set": {
      "new_field5": {$toDate: {$round: [{$divide: [1675750431775274,1000]},0]}}
}}],
{multi: true})

Playground

Upvotes: 1

W Kristianto
W Kristianto

Reputation: 9313

You should pass the rounded value directly to the new Date(), not an array:

db.mycollection.update({}, [
  {
    "$set": {
      "new_field5": new Date({
        "$round": [{ "$divide": [ "$ts", 1000 ] }]
      })
    }
  }
], { multi: true })

Upvotes: 1

Related Questions