Datetime hours range search in mongodb

Question

MongoDB Aggregation - Find Documents within Specific Hour Range and Date I have a MongoDB collection with documents containing an orderDate field of type ISODate. I want to find the documents where the orderDate falls within a specific hour range (e.g., 15:00:00 to 17:59:59) on a given date.

Input : For new Date("2023-06-07T10:30:00Z") from 15:00:00 to 17:59:60

[
  {
    _id: 1,
    orderDate: ISODate("2023-06-07T10:30:00Z"),
    status: "Completed"
  },
  {
    _id: 2,
    orderDate: ISODate("2023-06-07T17:00:00Z"),
    status: "Pending"
  },
  {
    _id: 3,
    orderDate: ISODate("2023-06-07T20:15:00Z"),
    status: "Completed"
  },
  {
    _id: 4,
    orderDate: ISODate("2023-06-08T09:00:00Z"),
    status: "Pending"
  },
  {
    _id: 5,
    orderDate: ISODate("2023-06-08T14:30:00Z"),
    status: "Completed"
  }
]

Output :

[
  {
    "_id": 2,
    "orderDate": ISODate("2023-06-07T17:00:00Z"),
    "status": "Pending"
  }
]

Aniket Raj · Accepted Answer · 2024-08-06 14:35:05Z

MongoDb Playground : https://mongoplayground.net/p/omFcFgufaBf

Here $dateFromParts we can use:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $gte: [
              "$orderDate",
              {
                $dateFromParts: {
                  year: {
                    $year: new Date("2023-06-07T10:30:00Z")
                  },
                  month: {
                    $month: new Date("2023-06-07T10:30:00Z")
                  },
                  day: {
                    $dayOfMonth: new Date("2023-06-07T10:30:00Z")
                  },
                  hour: 15,
                  minute: 0,
                  second: 0,
                  millisecond: 0
                }
              }
            ]
          },
          {
            $lte: [
              "$orderDate",
              {
                $dateFromParts: {
                  year: {
                    $year: new Date("2023-06-07T10:30:00Z")
                  },
                  month: {
                    $month: new Date("2023-06-07T10:30:00Z")
                  },
                  day: {
                    $dayOfMonth: new Date("2023-06-07T10:30:00Z")
                  },
                  hour: 17,
                  minute: 59,
                  second: 60,
                  millisecond: 1000
                }
              }
            ]
          }
        ]
      }
    }
  }
])

Collectives™ on Stack Overflow

Datetime hours range search in mongodb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related