55

In MongoDB, I have a document with a field called "ClockInTime" that was imported from CSV as a string.

What does an appropriate db.ClockTime.update() statement look like to convert these text based values to a date datatype?

Abhijit Bashetti
  • 8,518
  • 7
  • 35
  • 47
Jeff Fritz
  • 9,821
  • 7
  • 42
  • 52

5 Answers5

86

This code should do it:

> var cursor = db.ClockTime.find()
> while (cursor.hasNext()) {
... var doc = cursor.next();
... db.ClockTime.update({_id : doc._id}, {$set : {ClockInTime : new Date(doc.ClockInTime)}})
... }
kris
  • 23,024
  • 10
  • 70
  • 79
  • 2
    @Kristina - Phew, this saved a ton of my time and thanks to the author for asking such question. – Rakesh Sankar Oct 22 '11 at 13:35
  • 1
    Just as a side-note (and I stand to be corrected) but if you're converting from a time stamp value, a string won't work. It has to be a long int. For example a time stamp value of 206281199529 cannot be '206281199529' in the source file (CSV or JSON). I'm not sure why not - tried it out with v2.2.2 and every time it set the date to epoch. – backdesk Dec 16 '12 at 16:56
  • @Crungmungus that's true, only dates in the form that the date constructor takes will be converted properly. See https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/Date for all formats. – kris Dec 17 '12 at 14:23
  • @kristina I have a date in this string format "2003-10-24". I used the above code but all the dates got changed to 1970-01-01.... How I can use the Update function ? – Shubham Pendharkar Feb 29 '16 at 13:27
  • @Shubham you have to have a valid JavaScript date format, check out https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date. – kris Feb 29 '16 at 17:05
14

I have exactly the same situation as Jeff Fritz.

In my case I have succeed with the following simpler solution:

db.ClockTime.find().forEach(function(doc) { 
    doc.ClockInTime=new Date(doc.ClockInTime);
    db.ClockTime.save(doc); 
    })
Ciges
  • 1,133
  • 11
  • 17
5

This is a generic sample code in python using pymongo

from pymongo import MongoClient
from datetime import datetime

def fixTime(host, port, database, collection, attr, date_format):
    #host is where the mongodb is hosted eg: "localhost"
    #port is the mongodb port eg: 27017
    #database is the name of database eg : "test"
    #collection is the name of collection eg : "test_collection"
    #attr is the column name which needs to be modified
    #date_format is the format of the string eg : "%Y-%m-%d %H:%M:%S.%f"
    #http://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior
    client = MongoClient(host, port)
    db = client[database]
    col = db[collection]
    for obj in col.find():
        if obj[attr]:
            if type(obj[attr]) is not datetime:
                time = datetime.strptime(obj[attr],date_format)
                col.update({'_id':obj['_id']},{'$set':{attr : time}})

for more info : http://salilpa.com/home/content/how-convert-property-mongodb-text-date-type-using-pymongo

Salil Panikkaveettil
  • 693
  • 1
  • 6
  • 11
4

Starting Mongo 4.x:

  • db.collection.update() can accept an aggregation pipeline, finally allowing the update of a field based on its current value (Mongo 4.2+).
  • There is a new $toDate aggregation operator (Mongo 4.0).

Such that:

// { a: "2018-03-03" }
db.collection.updateMany(
  {},
  [{ $set: { a: { $toDate: "$a" } } }]
)
// { a: ISODate("2018-03-03T00:00:00Z") }

  • The first part {} is the match query, filtering which documents to update (in this case all documents).

  • The second part [{ $set: { a: { $toDate: "$a" } } }] is the update aggregation pipeline (note the squared brackets signifying the use of an aggregation pipeline). $set is a new aggregation operator which in this case replaces the field's value. The replaced value being the field itself concerted to an ISODate object. Note how a is modified directly based on its own value ($a).

Xavier Guihot
  • 54,987
  • 21
  • 291
  • 190
  • 2
    This is the best answer (actually the only correct one) and had zero upvotes :O You can also use `updateMany`: `db.collection.updateMany( {}, [{ $set: { a: { $toDate: "$a" } } }] )` – Kacper Cichecki Nov 07 '20 at 02:01
  • 2
    This is a life-saver solution... didn't believe it would work until I tried it out myself. – Telemat May 11 '21 at 05:01
  • Such a simple and straight-forward answer and solution, thanks a-lot! I almost thought Mongo couldn't achieve this simple task so this saved my day! – Guy_g23 May 07 '23 at 07:52
1

If you need to check if the field already has been converted you can use this condition:

/usr/bin/mongo mydb --eval 'db.mycollection.find().forEach(function(doc){
    if (doc.date instanceof Date !== true) {
        doc.date = new ISODate(doc.date);
        db.mycollection.save(doc);
    }
});'

Otherwise the command line may break.

webDEVILopers
  • 1,886
  • 1
  • 21
  • 35