CODEWITHSUNDEEP
mongodbmongodb-query
Slow Snail
Slow Snail

Reputation: 189

Convert string to datetime in mongodb

I have some fields suppose to be datetime but some date values are inserted as string from the application so now the fields become mixed data type. The string format is like below: "Tue May 13 00:00:00 CDT 2010".

I am trying to convert all the string type of the date to be datetime, I am using the following format, using CreateDate as the sample field with this problem:

db.Test.aggregate([
    { "$addFields": {
        "$CreateDate": { 
            "$dateFromString": { 
                "dateString": "$CreateDate",
                timezone: "America/Chicago"
            } 
        }
    } }
])

But I am getting the following error:

errmsg" : "Error parsing date string 'Tue May 13 00:00:00 CDT 2010'"

I am using Mongo 3.6 version and format seems to be new in 4.0 only. Any help will be appreciated.

Upvotes: 0

Views: 1982

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You should never store Date/Time values as string, use proper Date object. Your problem is yet another example of the validity of this statement.

Mongo version 4 would not help either. Month names are not supported, see Format Specifiers

I think you have to go for a 3rd party library, e.g. moment.js but you cannot use it directly, i.e. db.Test.aggregate([{$set: {CreateDate: moment("$CreateDate").toDate()}}])

You could correct wrong values with a loop like this:

db.Test.find({ CreateDate: { $not: { $type: "date" } } }).forEach(function(doc) {
    let d = moment(doc.CreateDate).toDate();
    db.Test.updateOne({ _id: doc._id }, { $set: { CreateDate: d } });    
})

Upvotes: 1

Related Questions