在MongoDB中使用Mongo Shell将字符串转换为日期或ISODate [英] Convert String to date or ISODate using Mongo Shell in MongoDB

问题描述

我在MongoDB中有数千个对象.我有一个名为"Insert_Date"的字段，其字符串格式为:"DD-Month(eg.JUN)-YYYY hh:mm".我想使用mongo shell将其转换为Date或ISODate 我尝试过此操作，但显示错误无效的ISO日期"

I have thousands of objects in MongoDB. And I have a field called "Insert_Date" which String format:"DD-Month(eg.JUN)-YYYY hh:mm". I would like to convert it to Date or ISODate using mongo shell I've tried this one but it showed error "invalid ISO date"

db.collection.find().forEach(function(doc) { 
doc.Insert_Date=new ISODate(doc.Insert_Date);
db.collection.save(doc); 
})

是否还有另一种转换方式可以使用正则表达式? 任何帮助都将得到申请.

Is there another way to convert it may be using regex? Any help would be appriciated.

推荐答案

这是使用"2009年6月1日00:00"作为日期值的一种方法.

Here's one way to do it using "01-JUN-2009 00:00" as the date value.

首先，解析时间值:

db.dates.aggregate([
   {
    $project : {
        day: { $substr: [ "$Date_Time", 0, 2 ] },
        month: { $substr: [ "$Date_Time", 3, 3 ] },
        year: { $substr: [ "$Date_Time", 7, 4 ] },
        hour: { $substr: [ "$Date_Time", 12, 2 ] },
        minute: { $substr: [ "$Date_Time", 15, 2 ] }
        }
    },
    { $out : "dates" }
]);

然后，如注释中所述，将月份MMM字符串转换为MM数字. 您需要使用3.4或更高版本才能使用switch语句:

Then, convert the month MMM string to MM numeric like you've mentioned in the comment. You will need to be on version 3.4 or higher to use the switch statement:

db.dates.aggregate( [
  {
    $project: {
        "day": "$day",
        "year": "$year",
        "hour": "$hour",
        "minute": "$minute",
        "month" :
        {
            $switch: { 
                branches: [
                    { case: { $eq: [ "$month", "JAN" ] }, then: "01" },
                    { case: { $eq: [ "$month", "FEB" ] }, then: "02" },
                    { case: { $eq: [ "$month", "MAR" ] }, then: "03" },
                    { case: { $eq: [ "$month", "APR" ] }, then: "04" },
                    { case: { $eq: [ "$month", "MAY" ] }, then: "05" },
                    { case: { $eq: [ "$month", "JUN" ] }, then: "06" },
                    { case: { $eq: [ "$month", "JUL" ] }, then: "07" },
                    { case: { $eq: [ "$month", "AUG" ] }, then: "08" },
                    { case: { $eq: [ "$month", "SEP" ] }, then: "09" },
                    { case: { $eq: [ "$month", "OCT" ] }, then: "10" },
                    { case: { $eq: [ "$month", "NOV" ] }, then: "11" },
                    { case: { $eq: [ "$month", "DEC" ] }, then: "12" }
                ]
            }
        }
      }
   },
   { $out : "dates" }
]);

然后，您可以创建MongoDB会将其解释为日期的字符串:

Then, you can create the string that MongoDB will interpret as a date:

db.dates.find().forEach(function(doc) { 
    db.dates.update({_id: doc._id},{$set : {"Date_Time": doc.year + '-' + doc.month + '-' + doc.day + 'T' + doc.hour + ':' + doc.minute}});
});

最后一步是通过将字符串传递给Date()来构造一个ISODate:

The last step is to construct an ISODate by passing the string into Date():

db.dates.find().forEach(function(doc) { 
    doc.Date_Time=new Date(doc.Date_Time);
    db.dates.save(doc); 
})

这篇关于在MongoDB中使用Mongo Shell将字符串转换为日期或ISODate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！