MongoDB: Search by field with date and update it by condition

Question

Please tell me how can I fulfill the following condition - if the time in the info.startDate field is not equal to 00 hours, increase the date (2021-05-27) by 1 day ahead, set the time to 00:00:00.000Z. I tried to do it clumsily, through Mongock, getting all the elements of the collection and doing a check through LocalDateTime, but my heap overflowed, which is logical because the collection is large. How can I do this through Mongock or at least a manual request to MongoDB. So far I've only written this:

db.getSiblingDB("ervk_core").getCollection("supervision").updateMany(
{},
[
  {
    "$set": {
      "info.startDate": {
        "$cond": {
          if: {
            $eq: [
              "$info.startDate",
              (there should be a condition at midnight)
            ]
          },
          then: (here the day should be added to full and the time should be set to midnight)
        }
      }
    }
  }
])

I would like to use dateToString to do a partial search by hour, but as I understand it, this function can only be used in an aggregation.

I would be grateful for your help :)

score 3 · Accepted Answer · answered Feb 27 '22 at 18:47

If you're using Mongo version 5.0+ then you can use $dateTrunc and $dateAdd to achieve this quite easily, like so:

db.collection.updateMany(
{},
[
  {
    $set: {
      "info.startDate": {
        $cond: [
          {
            $ne: [
              {
                $hour: "$info.startDate"
              },
              0
            ]
          },
          {
            $dateTrunc: {
              date: {
                $dateAdd: {
                  startDate: "$info.startDate",
                  unit: "day",
                  amount: 1
                }
              },
              unit: "day",
              
            }
          },
          "$info.startDate"
        ]
      }
    }
  }
])

For older Mongo versions this is slightly messier, you should use $dateFromParts to create the new date object, like so:

db.collection.updateMany(
{},
[
  {
    $set: {
      "info.startDate": {
        $cond: [
          {
            $ne: [
              {
                $hour: "$info.startDate"
              },
              0
            ]
          },
          {
            $dateFromParts: {
              "year": {
                $year: {
                  $add: [
                    "$info.startDate",
                    86400000
                  ]
                }
              },
              "month": {
                $month: {
                  $add: [
                    "$info.startDate",
                    86400000
                  ]
                }
              },
              "day": {
                $dayOfMonth: {
                  $add: [
                    "$info.startDate",
                    86400000
                  ]
                }
              },
              "hour": 0,
              "minute": 0,
              "second": 0,
              "millisecond": 0,
              
            }
          },
          "$info.startDate"
        ]
      }
    }
  }
])

Mongo Playground

MongoDB: Search by field with date and update it by condition

1 Answers1