Mongodb字符串到日期的转换 [英] Mongodb string to date conversion

问题描述

下面是我的示例mongodb集合

Below is my sample mongodb collection

{
    "_id" : ObjectId("57ed32f4070577ec56a56b9f"),
    "log_id" : "180308",
    "issue_id" : "108850",
    "author_key" : "priyadarshinim_contus",
    "timespent" : NumberLong(18000),
    "comment" : "Added charts in the dashboard page of the application.",
    "created_on" : "2017-08-16T18:22:04.816+0530",
    "updated_on" : "2017-08-16T18:22:04.816+0530",
    "started_on" : "2017-08-16T18:21:39.000+0530",
    "started_date" : "2017-08-02",
    "updated_date" : "2017-08-02",
    "role" : "PHP",
    "updated_at" : ISODate("2017-09-29T15:27:48.069Z"),
    "created_at" : ISODate("2017-09-29T15:27:48.069Z"),
    "status" : 1.0
}

我需要在 started_date 的帮助下获取记录，默认情况下，我将给出两个日期，因为我将检查开始日期的$gt和$lt.

I need to get records with help of started_date , by default I will give two dates in that i will check $gt and $lt of started date .

        $current_date =  '2017-08-31';
        $sixmonthfromcurrent ='2017-08-01';

     $worklogs = Worklog::raw ( function ($collection) use ($issue_jira_id, $current_date, $sixmonthfromcurrent) {
     return $collection->aggregate ( [ 
              ['$match' => ['issue_id' => ['$in' => $issue_jira_id],
                          'started_date' => ['$lte' => $current_date,'$gte' => $sixmonthfromcurrent] 
                    ] 
              ],

              ['$group' => ['issue_id' => ['$push' => '$issue_id'],
                          '_id' => ['year' => ['$year' => '$started_date'],
                          'week' => ['$week' => '$started_date'],'resource_key' => '$author_key'],
                          'sum' => array ('$sum' => '$timespent')] 
              ],
              [ '$sort' => ['_id' => 1] 
              ] 
        ] );
     } );

如果我运行此查询，则会收到此类错误:

If I run this query I am getting this type of error:

Can't convert from BSON type string to Date

如何纠正此错误?

推荐答案

您认为 $ group 中唯一令人困扰的字段是 week 字段. 通过在 $ group 聚合之前执行 $ project 可以提取 year :

The only field in your $group that I see as troubling is the field week. The year you could extract by doing a $project before your $group aggregation:

$project: {
        year: { $substr: [ "$started_date", 0, 4 ] }, 
        issue_id: 1,
        author_key: 1,
        timespent: 1
    }

如果您知道日期字符串将始终采用这种格式.当然，您不能执行substr运算来确定星期.

if you know that the date string will always come at this format. Of course you cannot do a substr operation for finding out the week.

如果您的字段 started_date 是一个实际的ISODate()，那将很容易，那么您可以使用您在

It would be easy though if your field started_date would be an actual ISODate(), then you could use exactly what you wrote as you probably already saw in the documentation. If you need the field week very bad, which I imagine you do, then I'd suggest you convert your field started_date to an ISODate(). You can do that with a bulkWrite:

db = db.getSiblingDB('yourDatabaseName');
var requests = [];
db.yourCollectionName.find().forEach(doc => { 
    var date = yourFunctionThatConvertsStringToDate(doc.started_date);
    requests.push( { 
        'updateOne': {
            'filter': { '_id': doc._id },
            'update': { '$set': {  
                "started_date": date
            } }
        }
    });
    if (requests.length === 500) {
        db.yourCollectionName.bulkWrite(requests);
        requests = [];
    }
});

if(requests.length > 0) {
     db.yourCollectionName.bulkWrite(requests);
}

将此脚本直接加载到您的mongodb服务器上并在此处执行. 希望这会有所帮助.

Load this script directly on your mongodb server and execute there. Hope this helps.

这篇关于Mongodb字符串到日期的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！