Aggregate Group by Date with Daylight Saving Offse

问题:

I'm trying to use mongo aggregation to group documents by the week of a timestamp on each document. All timestamps are stored in UTC time and I need to calculating the week using the clients time not UTC time.

I can provide and add the clients UTC offset as shown below but this doesn't always work due to daylight savings time. The offset is different depending on the date and therefore adjusting all the timestamps with the offset of the current date won't do.

Does anyone know of a way to group by week that consistently accounts for daylight savings time?

db.collection.aggregate([
    { $group: 
        { "_id": 
            { "$week": 
                { "$add": [ "$Timestamp", clientsUtcOffsetInMilliseconds ] } 
            } 
        },
        { "firstTimestamp": 
            { "$min": "$Timestamp"}
        }
    }   
]);

回答1:

The basic concept here is to make your query "aware" of when "daylight savings" both "starts" and "ends" for the given query period and simply supply this test to $cond in order to determine which "offset" to use:

db.collection.aggregate([
    { "$group": {
       "_id": {
           "$week": {
               "$add": [
                  "$Timestamp",
                  { "$cond": [
                      { "$and": [
                          { "$gte": [
                             { "$dayOfyear": "$Timestamp" },
                             daylightSavingsStartDay 
                          ]},
                          { "$lt": [ 
                             { "$dayOfYear": "$Timestamp" },
                             daylightSavingsEndDay
                          ]}
                      ]},
                      daylightSavingsOffset,
                      normalOffset
                  ]}
               ]
           }
       },
       "min": { "$min": "$Timestamp" }
    }}
])

So you can make that a little more complex when covering several years, but it still is the basic principle. In in the southern hemisphere you are always spanning a year so each condition would be a "range" of "start" to "end of year" and "begining of year" to "end". Therefore an $or with an inner $and within the "range" operators demonstrated.

If you have different values to apply, then detect when you "should" choose either and then apply using $cond.