MongoDB聚合中基于UTC时间按指定时区获取周数据

问题背景 #

数据以UTC格式存储，样例结构如下：

{
  Steps: {
    "quantity": 25,
    "duration": 100,
    "endTime": "2023-05-18 01:25:53 +0000",
    "startTime": "2023-05-18 01:25:52 +0000"
  },
  leadId: 10
}

当用户传入IST时区（+05:30）时，需按该时区的周范围统计数据。例如查询2023年5月1日-5月31日的数据，实际要匹配startTime在UTC时间2023-04-30 18:30:00到2023-05-30 18:29:59之间的记录（对应IST的5月1日00:00到5月31日23:59）。

现有问题 #

尝试的聚合查询未正确按时区转换周范围：期望的周范围是IST的2023-05-27 00:00到2023-06-03 23:59（对应UTC的2023-05-27 18:30:00到2023-06-03 18:29:59），但实际统计的是UTC的2023-05-28 00:00到2023-06-03 11:59:59的范围，未按目标时区划分周。

现有聚合查询代码：

activities.aggregate([ 
  { '$match': { Steps: { '$ne': null }, 'Steps.startTime': { '$gte': new Date('2023-04-30T18:30:00.000Z'), '$lt': new Date('2023-05-30T18:30:00.000Z') }, leadId: 36 } }, 
  { '$group': { _id: { startTime: '$Steps.startTime', endTime: '$Steps.endTime' }, doc: { '$last': '$$ROOT' } } }, 
  { '$group': { _id: { '$dateTrunc': { date: '$doc.Steps.startTime', unit: 'week', timezone: '+05:30', startOfWeek: 'sunday' } }, docs: { '$addToSet': '$doc' } } }, 
  { '$unwind': '$docs' }, 
  { '$project': { _id: '$_id', quantity: { '$toDouble': { '$ifNull': [ '$docs.Steps.quantity', 0 ] } }, duration: { '$toDouble': { '$ifNull': [ '$docs.Steps.duration', 0 ] } } } }, 
  { '$group': { _id: '$_id', quantity: { '$sum': '$quantity' }, duration: { '$sum': '$duration' } } }, 
  { '$sort': { _id: 1 } }, 
  { '$addFields': { startDate: new Date('2023-04-30T18:30:00.000Z'), endDate: new Date('2023-05-30T18:30:00.000Z'), step: 604800000, bounds: 'full' } }, 
  { '$addFields': { rangeStart: { '$dateToParts': { date: '$startDate', timezone: '+00:00' } }, rangeEnd: { '$dateToParts': { date: '$endDate', timezone: '+00:00' } } } }, 
  { '$addFields': { range: { '$range': [ 0, { '$ceil': { '$divide': [ { '$subtract': [ { '$multiply': [Array] }, { '$add': [Array] } ] }, 12 ] } }, 1 ] } } 
], {})

预期输出 #

需要返回包含所有周（即使数据为0）的统计结果，格式如下：

[
  {
    "duration": 0,
    "quantity": 0,
    "startTime": ISODate("2023-03-25T18:30:00Z"),
    "week": 1
  },
  {
    "duration": 0,
    "quantity": 0,
    "startTime": ISODate("2023-04-01T18:30:00Z"),
    "week": 2
  },
  {
    "duration": 0,
    "quantity": 0,
    "startTime": ISODate("2023-04-08T18:30:00Z"),
    "week": 3
  },
  {
    "duration": 100,
    "quantity": 25,
    "startTime": ISODate("2023-04-15T18:30:00Z"),
    "week": 4
  },
  {
    "duration": 0,
    "quantity": 0,
    "startTime": ISODate("2023-04-22T18:30:00Z"),
    "week": 5
  },
  {
    "duration": 0,
    "quantity": 0,
    "startTime": ISODate("2023-04-29T18:30:00Z"),
    "week": 6
  },
  {
    "duration": 100,
    "quantity": 25,
    "startTime": ISODate("2023-05-06T18:30:00Z"),
    "week": 7
  },
  {
    "duration": 200,
    "quantity": 50,
    "startTime": ISODate("2023-05-13T18:30:00Z"),
    "week": 8
  },
  {
    "duration": 100,
    "quantity": 25,
    "startTime": ISODate("2023-05-20T18:30:00Z"),
    "week": 9
  },
  {
    "duration": 0,
    "quantity": 0,
    "startTime": ISODate("2023-05-27T18:30:00Z"),
    "week": 10
  },
  {
    "duration": 0,
    "quantity": 0,
    "startTime": ISODate("2023-06-03T18:30:00Z"),
    "week": 11
  }
]

解决方案 #

核心是先基于目标时区生成所有周的时间范围，再关联统计数据，避免先过滤数据导致的时区偏差。以下是修正后的聚合查询：

const timezone = '+05:30';
const userStartDate = new Date('2023-05-01'); // 用户传入的IST起始日期
const userEndDate = new Date('2023-05-31'); // 用户传入的IST结束日期

// 转换为UTC的查询边界（对应IST的userStartDate 00:00到userEndDate 23:59）
const utcStart = new Date(userStartDate.getTime() - (5.5 * 60 * 60 * 1000));
const utcEnd = new Date(userEndDate.getTime() - (5.5 * 60 * 60 * 1000) + (24 * 60 * 60 * 1000) - 1);

activities.aggregate([
  // 1. 匹配符合时间范围的记录
  {
    $match: {
      Steps: { $ne: null },
      'Steps.startTime': { $gte: utcStart, $lt: utcEnd },
      leadId: 36
    }
  },
  // 2. 按IST时区的周分组统计数据
  {
    $group: {
      _id: {
        $dateTrunc: {
          date: '$Steps.startTime',
          unit: 'week',
          timezone: timezone,
          startOfWeek: 'sunday'
        }
      },
      totalQuantity: { $sum: { $toDouble: { $ifNull: ['$Steps.quantity', 0] } } },
      totalDuration: { $sum: { $toDouble: { $ifNull: ['$Steps.duration', 0] } } }
    }
  },
  // 3. 生成指定时间范围内所有IST周的起始时间（UTC格式）
  {
    $facet: {
      stats: [{ $sort: { _id: 1 } }],
      allWeeks: [
        { $limit: 1 },
        {
          $addFields: {
            // 计算起始周的UTC时间（IST的userStartDate所在周的周日00:00对应的UTC）
            startWeek: {
              $dateTrunc: {
                date: utcStart,
                unit: 'week',
                timezone: timezone,
                startOfWeek: 'sunday'
              }
            },
            // 计算结束周的UTC时间（IST的userEndDate所在周的周日00:00对应的UTC）
            endWeek: {
              $dateTrunc: {
                date: utcEnd,
                unit: 'week',
                timezone: timezone,
                startOfWeek: 'sunday'
              }
            },
            weekMs: 604800000 // 一周的毫秒数
          }
        },
        {
          $addFields: {
            weekCount: {
              $ceil: {
                $divide: [{ $subtract: ['$endWeek', '$startWeek'] }, '$weekMs']
              }
            }
          }
        },
        {
          $addFields: {
            weeks: {
              $map: {
                input: { $range: [0, { $add: ['$weekCount', 1] }] },
                as: 'index',
                in: {
                  $add: ['$startWeek', { $multiply: ['$$index', '$weekMs'] }]
                }
              }
            }
          }
        },
        { $unwind: '$weeks' },
        { $project: { _id: 0, startTime: '$weeks' } }
      ]
    }
  },
  // 4. 将统计数据与所有周合并，补全数据为0的周
  {
    $unwind: '$allWeeks'
  },
  {
    $lookup: {
      from: 'activities', // 替换为你的集合名称
      localField: 'allWeeks.startTime',
      foreignField: '_id',
      as: 'stat'
    }
  },
  {
    $unwind: {
      path: '$stat',
      preserveNullAndEmptyArrays: true // 保留无数据的周
    }
  },
  // 5. 格式化输出
  {
    $project: {
      startTime: '$allWeeks.startTime',
      duration: { $ifNull: ['$stat.totalDuration', 0] },
      quantity: { $ifNull: ['$stat.totalQuantity', 0] }
    }
  },
  // 6. 添加周序号
  {
    $sort: { startTime: 1 }
  },
  {
    $group: {
      _id: null,
      weeks: { $push: '$$ROOT' }
    }
  },
  {
    $unwind: {
      path: '$weeks',
      includeArrayIndex: 'week'
    }
  },
  {
    $project: {
      _id: 0,
      startTime: '$weeks.startTime',
      duration: '$weeks.duration',
      quantity: '$weeks.quantity',
      week: { $add: ['$week', 1] } // 周序号从1开始
    }
  },
  { $sort: { week: 1 } }
])

关键修正点 #

1. 时间范围转换：将用户传入的本地日期准确转换为UTC查询边界，确保匹配正确的记录。
2. 预生成所有周：使用$facet和$map生成指定时间范围内的所有周起始时间，避免遗漏无数据的周。
3. 关联统计数据：通过$lookup将预生成的周与统计结果关联，补全数据为0的周。
4. 时区一致性：所有周的划分和统计都严格基于指定的timezone参数，确保周范围符合目标时区的定义。

内容的提问来源于stack exchange，提问作者DineshKumar