Home > OS >  compare date with mongoose timestamps using aggregate
compare date with mongoose timestamps using aggregate

Time:12-31

This is the schema:

const mongoose = require("mongoose");

const OrderSchema = mongoose.Schema({

  order_status: {
    type: "String",
    enum: ['Pending', 'Declined', 'Approved','Paid', 'Complete'],
    default: "Pending",
  },

},{ timestamps: true });

module.exports = mongoose.model("order", OrderSchema);

Stored data in db:

[
        {
            "_id": "61ceb03b76ed7e3b809bb15b",
            "order_status": "Declined",
            "createdAt": "2021-12-31T07:24:43.129Z",
            "updatedAt": "2021-12-31T07:24:43.129Z",
            "__v": 0
        },
        {
            "_id": "61ceb04676ed7e3b809bb15e",
            "order_status": "Paid",
            "createdAt": "2021-12-31T00:00:00.000Z",
            "updatedAt": "2021-12-31T07:24:54.951Z",
            "__v": 0
        },
        {
            "_id": "61ceb04f76ed7e3b809bb161",
            "order_status": "Approved",
            "createdAt": "2022-01-01T00:00:00.000Z",
            "updatedAt": "2021-12-31T07:25:03.860Z",
            "__v": 0
        },
        {
            "_id": "61ceb05276ed7e3b809bb164",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:25:06.580Z",
            "updatedAt": "2021-12-31T07:25:06.580Z",
            "__v": 0
        },
        {
            "_id": "61ceb05576ed7e3b809bb167",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:25:09.686Z",
            "updatedAt": "2021-12-31T07:25:09.686Z",
            "__v": 0
        },
        {
            "_id": "61ceb06976ed7e3b809bb16d",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:25:29.569Z",
            "updatedAt": "2021-12-31T07:25:29.569Z",
            "__v": 0
        },
        {
            "_id": "61ceb06d76ed7e3b809bb170",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:25:33.858Z",
            "updatedAt": "2021-12-31T07:25:33.858Z",
            "__v": 0
        },
        {
            "_id": "61ceb07376ed7e3b809bb173",
            "order_status": "Pending",
            "createdAt": "2021-12-31T07:25:39.270Z",
            "updatedAt": "2021-12-31T07:25:39.270Z",
            "__v": 0
        },
        {
            "_id": "61ceb07776ed7e3b809bb176",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:25:43.672Z",
            "updatedAt": "2021-12-31T07:25:43.672Z",
            "__v": 0
        },
        {
            "_id": "61ceb07e76ed7e3b809bb179",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:25:50.963Z",
            "updatedAt": "2021-12-31T07:25:50.963Z",
            "__v": 0
        },
        {
            "_id": "61ceb08276ed7e3b809bb17c",
            "order_status": "Pending",
            "createdAt": "2021-12-31T07:25:54.675Z",
            "updatedAt": "2021-12-31T07:25:54.675Z",
            "__v": 0
        },
        {
            "_id": "61ceb08776ed7e3b809bb17f",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:25:59.616Z",
            "updatedAt": "2021-12-31T07:25:59.616Z",
            "__v": 0
        },
        {
            "_id": "61ceb08d76ed7e3b809bb182",
            "order_status": "Complete",
            "createdAt": "2021-12-31T07:26:05.141Z",
            "updatedAt": "2021-12-31T07:26:05.141Z",
            "__v": 0
        }
    ]

Query: the value of from = 2021-01-01T00:00:00.000Z and to = 2021-12-31T00:00:00.000Z

  let from = new Date("2021-01-01")
  let to = new Date("2021-12-31")
    return await OrderSchema.aggregate([
        {$match : { createdAt: { 
          $gte: from, $lte: to
          } }
        }
  ])

Using the above query I'm getting no record. when I change the time from createdAt: "2021-12-31T07:26:05.141Z" to createdAt: "2021-12-31T00:00:00.000Z" in DB then it work fine. How can I get records of 1 month or 1 week from the current date and resolve the time issue?

CodePudding user response:

I believe you need the $and operator:

OrderSchema.aggregate([{
        $match : {
                $and: {
                        createdAt: { $gte: from },
                        createdAt: { $lte: to }
                }
        }
])

CodePudding user response:

I think this work for you may be or click here and check results

2022-01-01 --> $lte higher date so set less then 2021-12-31 --> $gte lower date so set grater then

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          createdAt: {
            $gte: "2021-12-31T00:00:00.000Z"
          }
        },
        {
          createdAt: {
            $lte: "2022-01-01T00:00:00.000Z"
          }
        }
      ]
    }
  }
])
  • Related