mongodb将日期另存为字符串时查询日期范围 [英] mongodb Querying for a Date Range when date is saved as string

问题描述

我正在将数据作为大容量插入保存到邦戈中. JSON对象数组的数据包含日期，数字，字母数字数据，均保存为字符串.

I'm saving data into the bongo as bulk insert. The data that's an array of JSON object contain date, numeric, alphanumeric data all saved as string.

样本数据

[{
    "CARDNO": "661",
    "HOLDERNO": "661",
    "HOLDERNAME": "S",
    "IODATE": "4/1/2012",
    "IOTIME": "00:03:27",
    "IOGATENO": "01-3",
    "IOGATENAME": "FWork",
    "IOSTATUS": "Entry",
    "DEPARTMENTNO": "1",
    "UPDATE_STATUS": "1"
}, {
    "CARDNO": "711",
    "HOLDERNO": "711",
    "HOLDERNAME": "P",
    "IODATE": "4/1/2012",
    "IOTIME": "04:35:33",
    "IOGATENO": "01-7",
    "IOGATENAME": "FDWork",
    "IOSTATUS": "Exit",
    "DEPARTMENTNO": "3",
    "UPDATE_STATUS": "1"
}]

我的查询

var start = new Date(2012，4，15); var end = new Date(2012，4，1);

var start = new Date(2012, 4, 15); var end = new Date(2012, 4, 1);

collection.find({
    "IODATE": {
        $gte: start,
        $lt: end
    }
}).toArray(function (err, data) {
    if (err) {
        console.log(err);
    } else {
        console.log(data.length)
    }
    //res.send(data.length);
    res.send(JSON.stringify(data));
});

这不是返回结果，我认为这是因为"IODATE"的值在db中的字符串中.

It's not returning result, I think it is because the value of "IODATE" is in string inside db.

如何解决此问题?我可能需要进行批量插入，因为数据可能在2亿左右.

How to work around this issue? I may need to do bulk insert since the data can be of 200 million or so.

推荐答案

最后一次尝试，因为您没有接受好的建议的良好记录.

One last try at this, because you don't have a good record of accepting good advice.

您当前的日期格式会会被您咬住.即使在尝试解决这些问题的地方.这里是问题:

Your date formats as they stand are going to bite you. Even where trying to work around them. Here are the problems:

• 格式不是词法.这意味着即使使用字符串比较运算符，例如 $ gte ， $ lte 都不起作用. 词汇日期的格式为"yyyy-mm-dd"为"2012-01-04".可以与运营商合作.

• The format is not lexical. Which means that even with a string comparison operators like $gte, $lte are just not going to work. A lexical date would be "2012-01-04" in "yyyy-mm-dd" format. That would work with the operators.

您可以查看 $ substr (和它是完整的缺少文档，请在汇总，但是您的日期格式缺少day和month的两位数形式，即"04/01/2012"，因此这会破坏操作符的位置性质.另外，您将必须进行转换，然后再进行任何 $ match ，这意味着您炸毁任何可能减少您的管道输入的机会，因此您受困于不能过滤您的大结果集，由date.

You could look at $substr (and it's complete lack of documentation, search on SO for real usage) within aggregate but your date format is lacking the double digit form of day and month ie "04/01/2012", so that is going to blow up the positional nature of the operator. Also you would have to transform before any $match which means you blow up any chance of reducing your pipeline input, so you are stuck with not being able to filter your large resultset by date.

这是一个可怕的案例，但是对于大数据问题，除了转换日期之外，实际上没有其他实用的解决方案.您刚刚使用的形式的字符串不会剪切它.要么按优先顺序转换为:

It's a horrible case, but there really is no other practical solution to the large data problem here than to convert your dates. Strings in the form that you have just do not cut it. Either, in order of preference convert to:

• BSON日期
• 纪元时间戳记长(无论如何)
• 词汇字符串表示形式(如前所述)

您的主要情况似乎是过滤，因此更新数据集是唯一可行的选择.否则，您会被分页"结果所困扰，并且需要进行很多的手动工作，否则可以在服务器端完成.

Your main case seems to be filtering, so updating the dataset is the only pratical alternative. Otherwise you are stuck with "paging" results and doing a lot of manual work, that could otherwise be done server side.

这篇关于mongodb将日期另存为字符串时查询日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！