如何使用mongoose从mongodb获取两个日期之间的数据 [英] How to get data between the two dates from mongodb using mongoose

问题描述

我的问题很简单.尝试从 [01-06-2020 到 07-06-2020] 两个日期之间从 mongodb 获取记录.但不起作用.怎么做?

日期格式为 dd-mm-yyyy

mongodb 记录:

<预><代码>[{_id:ObjectId("5edd1df67b272e2d4cf36f70"),日期:"01-06-2020",pid:1,pname:"Micheck"},{_id:ObjectId("5edd1dk67b272e2d4cf31f72"),日期:03-06-2020"，pid:2,pname:"佐恩"},{_id:ObjectId("5edd1rf67b272e2d4cf16f73"),日期:07-06-2020"，pid:3,姓名:罗伯特"},{_id:ObjectId("5edd1dw67b272e2d4cf76f76"),日期:"01-05-2020",pid:6,pname:"约瑟夫"}]

data.controller.js:

module.exports.getReportTableData = (req, res, next) =>{让 collectionname = req.query.collection;让 date1 = "01-06-2020";//dd-mm-yyyy让 date2 = "07-06-2020";//dd-mm-yyyy让 tableReportdata = dbc.model(collectionname);tableReportdata.find({ date: date1,date2 }, function(err, docs) {如果(错误){控制台日志(错误)；返回;} 别的 {console.log("数据加载成功");res.json({ data: docs, success: true, msg: '数据加载.' });}});};

解决方案

此方案需要Mongo server version > 4.0.0

根据 OP 的评论，他使用的是 4.2.7 版

Mongo 当前将您的字段视为字符串，而不是日期，因此首先您必须将它们转换为日期.

我建议在写入数据库时​​将您的字段类型更改为日期，或者添加具有日期类型的新字段以获得更好的性能和更少的开销

要在运行时进行此转换，您必须使用带有 addFields dateFromString 匹配 gte 和 lte 运算符.

您的代码应如下所示:

let date1 = new Date("2020-06-01T00:00:00.000Z");//日期对象让 date2 = new Date("2020-06-07T00:00:00.000Z");让 tableReportdata = dbc.model(collectionname);tableReportdata.aggregate([{$addFields:{转换日期:{$dateFromString: {dateString: "$date",格式:%d-%m-%Y"，时区:UTC"}}}},{$匹配:{转换日期:{$gte: 日期 1,$lte:日期2，}}}]，函数(错误，文档){如果(错误){控制台日志(错误)；返回;} 别的 {console.log("加载数据成功");res.json({数据:文档，成功:真实，msg: '数据已加载.'});}});

游乐场链接

My question is simple.Tried to get record from mongodb between the two dates like[ 01-06-2020 to 07-06-2020].But not working.How to do it?

Date format is dd-mm-yyyy

mongodb records:

[
{ 
_id:ObjectId("5edd1df67b272e2d4cf36f70"),
 date:"01-06-2020", 
 pid:1,
 pname:"Micheck" 
},
{ 
_id:ObjectId("5edd1dk67b272e2d4cf31f72"),
 date:"03-06-2020", 
 pid:2,
 pname:"Zohn" 
},
{ 
_id:ObjectId("5edd1rf67b272e2d4cf16f73"),
 date:"07-06-2020", 
 pid:3,
 pname:"Robert" 
},
{ 
_id:ObjectId("5edd1dw67b272e2d4cf76f76"),
 date:"01-05-2020", 
 pid:6,
 pname:"Josebh" 
}
]

data.controller.js:

module.exports.getReportTableData = (req, res, next) => {
    let collectionname = req.query.collection; 

    let date1 = "01-06-2020"; //dd-mm-yyyy
    let date2 = "07-06-2020"; //dd-mm-yyyy

    let tableReportdata = dbc.model(collectionname);
    tableReportdata.find({ date: date1,date2 }, function(err, docs) {
        if (err) {
            console.log(err);
            return;
        } else {
            console.log("Successful loaded data");
            res.json({ data: docs, success: true, msg: 'Data loaded.' });
        }
    });
};

解决方案

This solution Needs Mongo server version > 4.0.0

According to OPs comment he's using version 4.2.7

Mongo is currently treating your fields as a string, and not a date, so first you'll have to convert them to dates.

I would recommend changing your field type to date while writing to the DB, or adding a new field with date type for better performance and less overhead

To do this conversion at runtime, you'll have to use the aggregation pipeline with the, addFields dateFromString match gte and lte operators.

Your code should look like:

let date1 = new Date("2020-06-01T00:00:00.000Z"); // date objects
let date2 = new Date("2020-06-07T00:00:00.000Z");

let tableReportdata = dbc.model(collectionname);
tableReportdata.aggregate([{
        $addFields: {
            convertedDate: {
                $dateFromString: {
                    dateString: "$date",
                    format: "%d-%m-%Y",
                    timezone: "UTC"
                }
            }
        }
    },
    {
        $match: {
            convertedDate: {
                $gte: date1,
                $lte: date2,

            }
        }
    }
], function(err, docs) {
    if (err) {
        console.log(err);
        return;
    } else {
        console.log("Successful loaded data");
        res.json({
            data: docs,
            success: true,
            msg: 'Data loaded.'
        });
    }
});

Playground Link

这篇关于如何使用mongoose从mongodb获取两个日期之间的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！