Mongo updateMany statement with date.withZoneSameInstant(xxx)-CodePudding

I have this collection in my Mongo database:

id    | place | local time
--------------------------
3     | A     | 12pm
4     | A     | 11pm
5     | B     | 4pm
6     |       | 7pm

The local times are stored as ISODates with UTC timezone. A local time of 12 is represented as 2022-01-01T12:00:00.000Z.

Outside of the database I have a partially complete mapping between places and timezones.

place | timezone
A     | Europe/London
B     | Europe/Brussels

Using this information I want to make my database rows timezone-aware, by adding new fields that capture the timezone and fix the offset for the date. This would be my ideal result:

id    | place | local time | newDate                         | timezone
----------------------------------------------------------------------------
3     | A     | 12pm       | ISODate("2022-07-01T11:00:00Z") | Europe/London
4     | A     | 11pm       | ISODate("2022-07-01T22:00:00Z") | Europe/London
5     | B     | 4pm        | ISODate("2022-07-01T14:00:00Z") | Europe/Brussels
6     |       | 7pm        | ISODate("2022-07-01T07:00:00Z") | UTC

Adding the timezone column is straight forward, I iterate over my mapping's table and for each one build a single update statement like this:

db.testcollection.updateMany({ place: "A" }, { $set: { "timezone" : "Europe/London" }});

Dealing with locations where the timezone for the place isn't known (and we fallback to just using UTC) is also straight forward:

db.testcollection.updateMany({ "timezone" : { $exists: false } }, { $set: { "timezone" : "UTC" }});

But what I cannot seem to do is apply the timezone change itself, to multiple rows (with the same place) at once, using a Mongo update statement. I'm not entirely sure if it's possible.

I have tried:

rs0 [direct: primary] test> db.testcollection.updateMany({ place: "A" }, { $set: { "utctime" : { $dateAdd: { startDate: "$localtime", amount: 1, unit: "hour" }}}});

which can change the times by adding/subtracting an hour or so, but actually applying a timezone change like I want is too difficult for $dateAdd function. Europe/London has GMT/BST depending on daylight savings, certain years have had and abandoned different historic values: https://en.wikipedia.org/wiki/Daylight_saving_time_by_country

Even a series of time based filter clauses seems incredibly complicated.

The update I want to apply is essentially the same as the Java function:

correctUTC = localTimeMarkedAsUTC          // 9pm local time
    .withZoneSameLocal(portTimezone)       // 9pm Europe/London
    .withZoneSameInstant(UTC)              // 8pm UTC

I can use the Date object in an update function, but I am still struggling on how I can manipulate $dateToString and friends to generate the output I want:

test> db.testcollections.updateMany({ place: "A" }, [{ $set: { "utctime" : { $convert : { input: { $dateToString: { date: "$localtime", format: "%Y-%m-%dT%H:%M:%S%z", timezone: "UTC" }}, to: "date" }}}}]);

Sets to the same thing it's already set to. Using timezone London merely adds an hour. I want the opposite, which is to subtract an hour from the UTC variant.

Because of the size of the DB, I'd prefer a solution that operates as updates on whole places in bulk, or doesn't use .forEach(function(e){ ... }) to hit every record, unless that's the last possible solution that can possibly work?

I'm on Mongo 5.0

CodePudding user response：

Try this one:

db.collection.updateMany(
  {timezone: {$ne: "UTC"} }, // UTC times do not need any correction
  [
    {
      $set: {
        newDate: {
          $dateFromString: {
            // "cut" wrong time zone
            dateString: {
              $dateToString: {
                date: "$newDate",
                format: "%Y-%m-%dT%H:%M:%S.%L"
              }
            },
            format: "%Y-%m-%dT%H:%M:%S.%L",
            timezone: "$timezone" // "attach" correct time zone
          }
        }
      }
    }
  ]
)

Mongo Playground

CodePudding user response：

You just need to wrap it with [] to create a pipeline:

db.collection.update({
  place: "A"
},
[{
  $set: {
    "utctime": {
      $dateAdd: {
        startDate: "$localtime",
        amount: 1,
        unit: "hour"
      }
    }
  }
}],
{
  multi: true
})

As you can see here. Which can off course work with a bulk operation