分享
三行代码  ›  专栏  ›  技术社区  ›  Shreyas Kumar

api返回与指定日期匹配的日期和不匹配的日期

  •  1
  • Shreyas Kumar  · 技术社区  · 1 周前

    我有以下结构的文件:

    {
        "_id" : ObjectId("5ecd26504df3372a38afffd9"),
        "balance" : 104000,
        "bankID" : "Bank-1",
        "userEmail" : "kumarshreyas073@gmail.com",
        "bankName" : "Corporation Bank",
        "accountNumber" : "03214569874563",
        "ifsCode" : "CORP0001236",
        "branch" : "Udupi",
        "address" : "Udupi",
        "city" : "Udupi",
        "state" : "Karnataka",
        "openingBalance" : 100000,
        "transactions" : [ 
            {
                "credit" : 2000,
                "debit" : 0,
                "_id" : ObjectId("5ecd26614df3372a38afffea"),
                "transactionID" : "CashTransaction-5ecd26614df3372a38afffe8",
                "date" : "30-05-2026",
                "particulars" : "By Cash-1",
                "voucherType" : "Cash"
            }, 
            {
                "credit" : 0,
                "debit" : 2000,
                "_id" : ObjectId("5ecd272d4df3372a38b00012"),
                "transactionID" : "Receipt-5ecd272d4df3372a38b00009",
                "date" : "29-07-2020",
                "particulars" : "To Suresh kumar",
                "voucherType" : "Receipt"
            }, 
            {
                "credit" : 0,
                "debit" : 2000,
                "_id" : ObjectId("5ecd272d4df3372a38b00014"),
                "transactionID" : "Receipt-5ecd272d4df3372a38b00003",
                "date" : "30-05-2024",
                "particulars" : "To Karthik",
                "voucherType" : "Receipt"
            }
        ],
        "idCounter" : 1,
        "__v" : 0
    }
    

    我写的代码如下:

    exports.trail_balance = async (req, res, next) => {
      var trailBalance = {
        userEmail: req.body.userEmail,
        fromDate: req.body.fromDate,
        toDate: req.body.toDate,
      };
    
    var bankAccount = await Bank.aggregate([
        { $match: { userEmail: req.body.userEmail } },
        {
          $addFields: {
            transactions: {
              $filter: {
                input: "$transactions",
                as: "transactions",
                cond: {
                  $and: [
                    {
                      $gte: ["$$transactions.date", trailBalance.fromDate],
                    },
    
                    {
                      $lte: ["$$transactions.date", trailBalance.toDate],
                    },
                  ],
                },
              },
            },
          },
        },
      ]);
    
    res.status(200).json({
        result: 1,
        bankAccount: bankAccount.length > 0 ? bankAccount : [], 
      });
    };
    
    

    {
        "result": 1,
        "bankAccount": [
            {
                "_id": "5ecd26504df3372a38afffd9",
                "balance": 104000,
                "bankID": "Bank-1",
                "userEmail": "kumarshreyas073@gmail.com",
                "bankName": "Corporation Bank",
                "accountNumber": "03214569874563",
                "ifsCode": "CORP0001236",
                "branch": "Udupi",
                "address": "Udupi",
                "city": "Udupi",
                "state": "Karnataka",
                "openingBalance": 100000,
                "transactions": [
                    {
                       "credit" : 0,
                       "debit" : 2000,
                       "_id" : ObjectId("5ecd272d4df3372a38b00012"),
                       "transactionID" : "Receipt-5ecd272d4df3372a38b00009",
                       "date" : "29-07-2020",
                       "particulars" : "To Suresh kumar",
                       "voucherType" : "Receipt"
                   }
                ],
                "idCounter": 1,
                "__v": 0
          }
    

    但是,我得到了所有的交易。

    数据库中存储的所有日期都是字符串类型。

    3 回复  |  直到 1 周前
        1
  •  2
  •   Mohammad Hossein Shojaeinia    1 周前

    问题是你的日期格式。你开始约会的时候 day 在数据库中保存的数据中,日期类型为 string ,因此与您的查询相比,它总是以day开头,这是不正确的。因为在日期比较中,首先要比较年份,然后是月份,最后是日期。但你做得不对。

    在这个场景中,mongodb正在写!因为在你的 from date , 2 小于或等于 2 3 在你的 to date 大于或等于 . 所以它做得很好。

    yyyy-mm-dd 你的询问是正确的。

    如果无法更改数据,也可以在 aggregate

    https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/

        2
  •  2
  •   Gibbs    1 周前

    你快到了。问题出在你的数据上。它的日期格式无效。

    mongo playground

    我改为正确的日期格式之一的交易如下

    "date": ISODate("2026-05-30"),
    

    mongo date format

    因此,如果您有适当的格式,那么查询将起作用。

    new Date("<YYYY-mm-dd>") 返回具有指定日期的ISODate。

    new Date("<YYYY-mm-ddTHH:MM:ss>")

    new Date("<YYYY-mm-ddTHH:MM:ssZ>") 指定以UTC表示的日期时间,并返回以UTC表示的指定日期时间的ISODate。

    new Date(<integer>)

        3
  •  1
  •   turivishal    1 周前

    建议很少修复,

    • 使用将字符串日期转换为ISO日期 new Date("2020-07-31")
    var trailBalance = {
        userEmail: req.body.userEmail,
        fromDate: new Date(req.body.fromDate),
        toDate: new Date(req.body.toDate),
    };
    
    • transactions.date 字符串到ISO日期使用 $dateFromString
    • 格式: %d-%m-%Y 事务处理.日期
    {
        $dateFromString: {
            dateString: "$$transactions.date",
            format: "%d-%m-%Y"
        }
    }
    

    看看工作操场: https://mongoplayground.net/p/-KWgRCSwD8h

    var bankAccount = await Bank.aggregate([
      {
        $match: {
          userEmail: trailBalance.userEmail
        }
      },
      {
        $addFields: {
          transactions: {
            $filter: {
              input: "$transactions",
              as: "transactions",
              cond: {
                $and: [
                  {
                    $gte: [
                      {
                        $dateFromString: {
                          dateString: "$$transactions.date",
                          format: "%d-%m-%Y"
                        }
                      },
                      trailBalance.fromDate
                    ]
                  },
                  {
                    $lte: [
                      {
                        $dateFromString: {
                          dateString: "$$transactions.date",
                          format: "%d-%m-%Y"
                        }
                      },
                      trailBalance.toDate
                    ]
                  }
                ]
              }
            }
          }
        }
      }
    ]);