Use updateMany to create a new date field based on a millis field

Okay, so I have a collection that contains a “createdAtMillis” field (int64) and I’d like to introduce a new field, based on the createdAtMillis field but of course just have it as a regular date.

Just to get my feet wet, I tried:
db.observation.updateMany({},{$set:{“createdUTCDate”: new Date ()}})
And that worked like a charm! I’m King of the Castle.

Then I tried (just to see if it could handle large integers):
db.observation.updateMany({},{$set:{“createdUTCDate”: new Date (1693958185963)}})
And that worked perfectly, so I’m on top of the world feeling accomplished and proud of myself.

4 hours later…

Reality sets in…

All I need to do is:
db.observation.updateMany({},{$set:{“createdUTCDate”: new Date (“$createdAtMillis”)}})

And of course it doesn’t quite understand what “$createdAtMillis” really is, so it inserts 12/31/1969, 5:00:00 PM…which isn’t too far from my birthyear, so my eyes began to rain. I tried what seemed to be everything I could think of including a round trip back to trying things over and over again expecting different results…(and I believe we all know what clinical term is assigned to this type of behavior).

Also, I need this in UTC date/time, not my local time…but I couldn’t get by the first part, so here I am on Sunday night.

Any help would be greatly appreciated.

Hi @Allan_Chase,

Have you tried using $toDate to see if that works for you? If the below doesn’t work for you, please provide some sample documents, advise the expected output and MongoDB version you are using.

The below was run on a 7.0.2 test environment and using updateOne().

Inserting the test document:

test> db.collection.find()

test> db.collection.insertOne({'createdAtMillis': 1693958185963})
{
  acknowledged: true,
  insertedId: ObjectId('65643590c23a657c48db154f')
}
test> db.collection.find()
[
  {
    _id: ObjectId('65643590c23a657c48db154f'),
    createdAtMillis: 1693958185963
  }
]

Updating the document and setting the createdUTCDate field:

test> db.collection.updateOne({},[{'$set':{'createdUTCDate':{'$toDate':'$createdAtMillis'}}}])
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
test> db.collection.find()
[
  {
    _id: ObjectId('65643590c23a657c48db154f'),
    createdAtMillis: 1693958185963,
    createdUTCDate: ISODate('2023-09-05T23:56:25.963Z')
  }
]
test> 

FWIW here is the specific update pipeline used:

[{'$set':{'createdUTCDate':{'$toDate':'$createdAtMillis'}}}]

You may wish to test the updateMany() variant on a test environment first to see if there are any caveats before as well as making a backup of the data before trying this on production.

Regards,
Jason

OMG, I was sooooo close! I did start with “toDate” early, but what I didn’t do was the and ecapsulate ‘$toDate’ in single quotes! It worked great, thank you. It will now be an exercise up to the student (me) to figure out how to make those as UTC dates. Thanks Jason!!! I really appreciate it. Also, thanks for taking the time as well to walk through the examples you provided.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.