mongodb 在mongodb中将字符串转换为日期

Question

提问by user883257

Is there a way to convert string to date using custom format using mongodb shell

有没有办法使用 mongodb shell 使用自定义格式将字符串转换为日期

I am trying to convert "21/May/2012:16:35:33 -0400" to date,

我正在尝试将“21/May/2012:16:35:33 -0400”转换为日期，

Is there a way to pass DateFormatteror something to Date.parse(...)or ISODate(....)method?

有没有办法传递DateFormatter或传递给 Date.parse(...)或ISODate(....)方法？

Answer 1

采纳答案by Mark Hillick

You can use the javascript in the second link provided by Ravi Khakhkhar or you are going to have to perform some string manipulation to convert your orginal string (as some of the special characters in your original format aren't being recognised as valid delimeters) but once you do that, you can use "new"

您可以使用 Ravi Khakhkhar 提供的第二个链接中的 javascript，或者您将不得不执行一些字符串操作来转换您的原始字符串（因为原始格式中的某些特殊字符未被识别为有效的分隔符）但是一旦你这样做，你可以使用“新”

training:PRIMARY> Date()
Fri Jun 08 2012 13:53:03 GMT+0100 (IST)
training:PRIMARY> new Date()
ISODate("2012-06-08T12:53:06.831Z")

training:PRIMARY> var start = new Date("21/May/2012:16:35:33 -0400")        => doesn't work
training:PRIMARY> start
ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")

training:PRIMARY> var start = new Date("21 May 2012:16:35:33 -0400")        => doesn't work    
training:PRIMARY> start
ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")

training:PRIMARY> var start = new Date("21 May 2012 16:35:33 -0400")        => works
training:PRIMARY> start
ISODate("2012-05-21T20:35:33Z")

Here's some links that you may find useful (regarding modification of the data within the mongo shell) -

以下是您可能会发现有用的一些链接（关于在 mongo shell 中修改数据）-

http://cookbook.mongodb.org/patterns/date_range/

http://www.mongodb.org/display/DOCS/Dates

http://www.mongodb.org/display/DOCS/Overview+-+The+MongoDB+Interactive+Shell

Answer 2

回答by Ciges

In my case I have succeed with the following solution for converting field ClockInTime from ClockTime collection from string to Date type:

就我而言，我使用以下解决方案成功地将 ClockTime 集合中的字段 ClockInTime从字符串转换为日期类型：

db.ClockTime.find().forEach(function(doc) { 
    doc.ClockInTime=new Date(doc.ClockInTime);
    db.ClockTime.save(doc); 
    })

Answer 3

回答by chridam

Using MongoDB 4.0 and newer

使用 MongoDB 4.0 及更新版本

The $toDateoperator will convert the value to a date. If the value cannot be converted to a date, $toDateerrors. If the value is null or missing, $toDatereturns null:

该$toDate运营商将值转换为日期。如果该值无法转换为日期，则会$toDate出错。如果值为 null 或缺失，则$toDate返回 null：

You can use it within an aggregate pipeline as follows:

您可以在聚合管道中使用它，如下所示：

db.collection.aggregate([
    { "$addFields": {
        "created_at": {
            "$toDate": "$created_at"
        }
    } }
])

The above is equivalent to using the $convertoperator as follows:

以上相当于使用$convert运算符如下：

db.collection.aggregate([
    { "$addFields": {
        "created_at": { 
            "$convert": { 
                "input": "$created_at", 
                "to": "date" 
            } 
        }
    } }
])

Using MongoDB 3.6 and newer

使用 MongoDB 3.6 及更新版本

You cab also use the $dateFromStringoperator which converts the date/time string to a date object and has options for specifying the date format as well as the timezone:

您还可以使用$dateFromString运算符将日期/时间字符串转换为日期对象，并具有用于指定日期格式和时区的选项：

db.collection.aggregate([
    { "$addFields": {
        "created_at": { 
            "$dateFromString": { 
                "dateString": "$created_at",
                "format": "%m-%d-%Y" /* <-- option available only in version 4.0. and newer */
            } 
        }
    } }
])

Using MongoDB versions >= 2.6 and < 3.2

使用 MongoDB 版本 >= 2.6 and < 3.2

If MongoDB version does not have the native operators that do the conversion, you would need to manually iterate the cursor returned by the find()method by either using the forEach()method or the cursor method next()to access the documents. Withing the loop, convert the field to an ISODate object and then update the field using the $setoperator, as in the following example where the field is called created_atand currently holds the date in string format:

如果 MongoDB 版本没有执行转换的本机运算符，则需要find()通过使用forEach()方法或游标方法next()来手动迭代该方法返回的游标来访问文档。通过循环，将该字段转换为 ISODate 对象，然后使用$set运算符更新该字段，如下例所示，该字段被调用created_at且当前以字符串格式保存日期：

var cursor = db.collection.find({"created_at": {"$exists": true, "$type": 2 }}); 
while (cursor.hasNext()) { 
    var doc = cursor.next(); 
    db.collection.update(
        {"_id" : doc._id}, 
        {"$set" : {"created_at" : new ISODate(doc.created_at)}}
    ) 
};

For improved performance especially when dealing with large collections, take advantage of using the Bulk APIfor bulk updates as you will be sending the operations to the server in batches of say 1000 which gives you a better performance as you are not sending every request to the server, just once in every 1000 requests.

为了提高性能，尤其是在处理大型集合时，请利用Bulk API进行批量更新，因为您将以 1000 次为单位将操作发送到服务器，这会为您提供更好的性能，因为您不会将每个请求都发送到服务器，每 1000 个请求中只有一次。

The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions >= 2.6 and < 3.2. It updates all the documents in the collection by changing the created_atfields to date fields:

下面演示了这种方法，第一个示例使用 MongoDB 版本中可用的 Bulk API >= 2.6 and < 3.2。它通过将created_at字段更改为日期字段来更新集合中的所有文档：

var bulk = db.collection.initializeUnorderedBulkOp(),
    counter = 0;

db.collection.find({"created_at": {"$exists": true, "$type": 2 }}).forEach(function (doc) {
    var newDate = new ISODate(doc.created_at);
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "created_at": newDate}
    });

    counter++;
    if (counter % 1000 == 0) {
        bulk.execute(); // Execute per 1000 operations and re-initialize every 1000 update statements
        bulk = db.collection.initializeUnorderedBulkOp();
    }
})
// Clean up remaining operations in queue
if (counter % 1000 != 0) { bulk.execute(); }

Using MongoDB 3.2

使用 MongoDB 3.2

The next example applies to the new MongoDB version 3.2which has since deprecated the Bulk APIand provided a newer set of apis using bulkWrite():

下一个示例适用于新的 MongoDB 版本3.2，该版本已弃用 Bulk API并使用以下方法提供一组较新的 api bulkWrite()：

var bulkOps = [],
    cursor = db.collection.find({"created_at": {"$exists": true, "$type": 2 }});

cursor.forEach(function (doc) { 
    var newDate = new ISODate(doc.created_at);
    bulkOps.push(         
        { 
            "updateOne": { 
                "filter": { "_id": doc._id } ,              
                "update": { "$set": { "created_at": newDate } } 
            }         
        }           
    );

    if (bulkOps.length === 500) {
        db.collection.bulkWrite(bulkOps);
        bulkOps = [];
    }     
});

if (bulkOps.length > 0) db.collection.bulkWrite(bulkOps);

Answer 4

回答by Pascalsz

I had some strings in the MongoDB Stored wich had to be reformated to a proper and valid dateTime field in the mongodb.

我在 MongoDB 存储中有一些字符串，必须将其重新格式化为 mongodb 中正确且有效的日期时间字段。

here is my code for the special date format: "2014-03-12T09:14:19.5303017+01:00"

这是我的特殊日期格式代码：“2014-03-12T09:14:19.5303017+01:00”

but you can easyly take this idea and write your own regex to parse the date formats:

但是您可以轻松地采用这个想法并编写自己的正则表达式来解析日期格式：

// format: "2014-03-12T09:14:19.5303017+01:00"
var myregexp = /(....)-(..)-(..)T(..):(..):(..)\.(.+)([\+-])(..)/;

db.Product.find().forEach(function(doc) { 
   var matches = myregexp.exec(doc.metadata.insertTime);

   if myregexp.test(doc.metadata.insertTime)) {
       var offset = matches[9] * (matches[8] == "+" ? 1 : -1);
       var hours = matches[4]-(-offset)+1
       var date = new Date(matches[1], matches[2]-1, matches[3],hours, matches[5], matches[6], matches[7] / 10000.0)
       db.Product.update({_id : doc._id}, {$set : {"metadata.insertTime" : date}})
       print("succsessfully updated");
    } else {
        print("not updated");
    }
})

Answer 5

回答by Jthorpe

How about using a library like momentjsby writing a script like this:

通过编写这样的脚本来使用像momentjs这样的库如何：

[install_moment.js]
function get_moment(){
    // shim to get UMD module to load as CommonJS
    var module = {exports:{}};

    /* 
    copy your favorite UMD module (i.e. moment.js) here
    */

    return module.exports
}
//load the module generator into the stored procedures: 
db.system.js.save( {
        _id:"get_moment",
        value: get_moment,
    });

Then load the script at the command line like so:

然后在命令行加载脚本，如下所示：

> mongo install_moment.js

Finally, in your next mongo session, use it like so:

最后，在您的下一个 mongo 会话中，像这样使用它：

// LOAD STORED PROCEDURES
db.loadServerScripts();

// GET THE MOMENT MODULE
var moment = get_moment();

// parse a date-time string
var a = moment("23 Feb 1997 at 3:23 pm","DD MMM YYYY [at] hh:mm a");

// reformat the string as you wish:
a.format("[The] DDD['th day of] YYYY"): //"The 54'th day of 1997"

Answer 6

回答by Ashh

You can use $dateFromStringaggregation which converts string date to ISO date

您可以使用$dateFromString将字符串日期转换为 ISO 日期的聚合

db.collection.aggregate([
  {
    "$project": {
      "date": {
        "$dateFromString": {
          "dateString": "$date"
        }
      }
    }
  }
])

mongodb 在mongodb中将字符串转换为日期

提问by user883257

采纳答案by Mark Hillick

回答by Ciges

回答by chridam

回答by Pascalsz

回答by Jthorpe

回答by Ashh

相关推荐

最近更新

标签

mongodb 在mongodb中将字符串转换为日期

提问by user883257

采纳答案by Mark Hillick

回答by Ciges

回答by chridam

回答by Pascalsz

回答by Jthorpe

回答by Ashh

相关推荐

mongodb 如何在mongo中通过查询有效地删除文档？

如何在 MongoDB 集合中获取特定的嵌入文档？

mongodb mongo db 查询大于和小于的数组值

无法在 Ubuntu 11.04 上重新启动 mongodb

相关推荐

最近更新

标签