CODEWITHSUNDEEP
mongodbmongodb-query
user12392864
user12392864

Reputation: 308

Find dates with 1 hour difference from the first datetime of each day on MongoDB

Below an extract of my data

{ _id: ObjectId("1"), date: 2021-07-01T08:00.000+00:00, price: 10, id: 1}
{ _id: ObjectId("2"), date: 2021-07-01T08:20.000+00:00, price: 10.5, id: 1}
{ _id: ObjectId("3"), date: 2021-07-01T08:40.000+00:00, price: 9.8, id: 1}
{ _id: ObjectId("4"), date: 2021-07-01T09:00.000+00:00, price: 10.2, id: 1}
{ _id: ObjectId("5"), date: 2021-07-01T09:20.000+00:00, price: 10.9, id: 1}
{ _id: ObjectId("6"), date: 2021-07-01T09:40.000+00:00, price: 13, id: 1}
{ _id: ObjectId("7"), date: 2021-07-02T07:00.000+00:00, price: 10, id: 2}
{ _id: ObjectId("8"), date: 2021-07-02T07:20.000+00:00, price: 10.2, id: 2}
{ _id: ObjectId("9"), date: 2021-07-02T07:40.000+00:00, price: 8, id: 2}
{ _id: ObjectId("10"), date: 2021-07-02T08:00.000+00:00, price: 10.7, id: 2}
{ _id: ObjectId("11"), date: 2021-07-02T08:20.000+00:00, price: 10, id: 2}
{ _id: ObjectId("12"), date: 2021-07-02T08:40.000+00:00, price: 11, id: 2}
{ _id: ObjectId("13"), date: 2021-07-03T08:00.000+00:00, price: 11.5, id: 2}
{ _id: ObjectId("14"), date: 2021-07-03T08:20.000+00:00, price: 10.8, id: 2}

Is there a way to SELECT for each IDs the first hours within a day on each day ?

The result based on the extract would be

{ _id: ObjectId("1"), date: 2021-07-01T08:00.000+00:00, price: 10, id: 1}
{ _id: ObjectId("2"), date: 2021-07-01T08:20.000+00:00, price: 10.5, id: 1}
{ _id: ObjectId("3"), date: 2021-07-01T08:40.000+00:00, price: 9.8, id: 1}
{ _id: ObjectId("7"), date: 2021-07-02T07:00.000+00:00, price: 10, id: 2}
{ _id: ObjectId("8"), date: 2021-07-02T07:20.000+00:00, price: 10.2, id: 2}
{ _id: ObjectId("9"), date: 2021-07-02T07:40.000+00:00, price: 8, id: 2}
{ _id: ObjectId("13"), date: 2021-07-03T08:00.000+00:00, price: 11.5, id: 2}
{ _id: ObjectId("14"), date: 2021-07-03T08:20.000+00:00, price: 10.8, id: 2}

Upvotes: 0

Views: 335

Answers (1)

Takis
Takis

Reputation: 8705

Query does

  • makes string dates to date objects
  • group by year and day
    collect all documents in day-info
    finds the minimun hour, for each day
  • filter all the day-info, and keep only those that happened in the minimun hour
  • restore document structure

Test code here

Query (if you have dates, and not strings, skip the first $set stage)

aggregate(
[ {
  "$set" : {
    "date" : {
      "$dateFromString" : {
        "dateString" : "$date"
      }
    }
  }
}, {
  "$group" : {
    "_id" : {
      "year" : {
        "$year" : "$date"
      },
      "day" : {
        "$dayOfYear" : "$date"
      }
    },
    "day-info" : {
      "$push" : "$$ROOT"
    },
    "min-hour" : {
      "$min" : {
        "$hour" : "$date"
      }
    }
  }
}, {
  "$project" : {
    "day-info" : {
      "$filter" : {
        "input" : "$day-info",
        "as" : "d",
        "cond" : {
          "$eq" : [ {
            "$hour" : "$$d.date"
          }, "$min-hour" ]
        }
      }
    }
  }
}, {
  "$unwind" : {
    "path" : "$day-info"
  }
}, {
  "$replaceRoot" : {
    "newRoot" : "$day-info"
  }
} ]
)

Edit (to keep the dates that their difference is < 1 hour from the minimun date of each day you can do this)

Test code here

Query

db.collection.aggregate([
  {
    "$set": {
      "date": {
        "$dateFromString": {
          "dateString": "$date"
        }
      }
    }
  },
  {
    "$group": {
      "_id": {
        "year": {
          "$year": "$date"
        },
        "day": {
          "$dayOfYear": "$date"
        }
      },
      "day-info": {
        "$push": "$$ROOT"
      },
      "min-date": {
        "$min": "$date"
      }
    }
  },
  {
    "$set": {
      "day-info": {
        "$filter": {
          "input": "$day-info",
          "as": "d",
          "cond": {
            "$lt": [
              {
                "$subtract": [
                  "$$d.date",
                  "$min-date"
                ]
              },
              {
                "$multiply": [
                  60,
                  60,
                  1000
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$unwind": {
      "path": "$day-info"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$day-info"
    }
  },
  {
    "$sort": {
      "date": 1
    }
  }
])

Upvotes: 1

Related Questions