汇总按日期分组并带有夏令时偏移量 [英] Aggregate Group by Date with Daylight Saving Offset

问题描述

我正在尝试使用mongo聚合按每个文档的时间戳记周对文档进行分组.所有时间戳都存储在UTC时间中，我需要使用客户时间而不是UTC时间来计算星期.

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.

我可以如下所示提供和添加客户端UTC偏移量，但是由于夏令时，该方法并不总是有效.偏移量因日期而异，因此无法使用当前日期的偏移量来调整所有时间戳.

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"}
        }
    }   
]);

推荐答案

这里的基本概念是让您的查询了解"在给定查询期间夏令时"的开始"和结束"时间，并简单地将此测试提供给

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" }
    }}
])

因此，在涵盖数年后，您可以使这一点变得更加复杂，但这仍然是基本原则.在南半球，您总是跨越一年，因此每种情况都是开始"到年底"以及开始年份"到终点"的范围".因此，展示了一个内部范围$and在运算符内的$or.

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.

如果您要应用其他值，请检测何时应"选择其中一个，然后使用 $cond .

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

这篇关于汇总按日期分组并带有夏令时偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！