Hourly group with dateTrunc, binSize and timezone

Working with Data
1

Hello,

We are using MongoDB 7.0 to store iot sensor data in a time-series collection. The timestamp is stored in UTC.

The issue we are having is that when we are using dateTrunc with unit day and timezone Europe/Athens, the function works correctly. If we change the unit to hours and binSize 24, we are getting wrong results.

Please note that in the time range we are aggregating, the offset of the timezone changes from +02:00 to +03:00.

dateTrunc with unit day

db.entityMeasurement.aggregate( [
    _id: {
        $dateTrunc: {
          date: "$ts",
...   {
...     $match: { "mt.eid": "65438e24f19109180ab5dc6b",
...               "ms.entity_total_forward_active_energy_15m" : { $exists : true },
...                        "ts": { $gte: ISODate("2020-03-26T22:00:00.000Z"), $lt: ISODate("2020-03-31T21:00:00.000Z") }
...             }
...   },
...
...   {
...     $group: {
...       _id: {
...         $dateTrunc: {
...           date: "$ts",
...           unit: "day",
...           binSize: 1,
...               timezone: "Europe/Athens"
...         }
...       },
...         sumValue: {
...             $sum: "$ms.entity_total_forward_active_energy_15m"
...         }
...     }
...    },
...    {
...      $sort : { _id : 1 }
...    },
...    {
...      $project: { _id: 0, ts: "$_id", sumValue: 1}
...    }
... ] )
[
  { sumValue: 523680, ts: ISODate("2020-03-26T22:00:00.000Z") },
  { sumValue: 500800, ts: ISODate("2020-03-27T22:00:00.000Z") },
  { sumValue: 482960, ts: ISODate("2020-03-28T22:00:00.000Z") },
  { sumValue: 500160, ts: ISODate("2020-03-29T21:00:00.000Z") },
  { sumValue: 510800, ts: ISODate("2020-03-30T21:00:00.000Z") }
]

dateTrunc with unit hour and binSize 24

db.entityMeasurement.aggregate( [
  date: "$ts",
...   {

        }
      },
        sumValue: {
...     $match: { "mt.eid": "65438e24f19109180ab5dc6b",
...               "ms.entity_total_forward_active_energy_15m" : { $exists : true },
...                        "ts": { $gte: ISODate("2020-03-26T22:00:00.000Z"), $lt: ISODate("2020-03-31T21:00:00.000Z") }
...             }
...   },
...
...   {
...     $group: {
...       _id: {
...         $dateTrunc: {
...           date: "$ts",
...           unit: "hour",
...           binSize: 24,
...               timezone: "Europe/Athens"
...         }
...       },
...         sumValue: {
...             $sum: "$ms.entity_total_forward_active_energy_15m"
...         }
...     }
...    },
...    {
...      $sort : { _id : 1 }
...    },
...    {
...      $project: { _id: 0, ts: "$_id", sumValue: 1}
...    }
... ] )
[
  { sumValue: 523680, ts: ISODate("2020-03-26T22:00:00.000Z") },
  { sumValue: 500800, ts: ISODate("2020-03-27T22:00:00.000Z") },
  { sumValue: 503920, ts: ISODate("2020-03-28T22:00:00.000Z") },
  { sumValue: 500320, ts: ISODate("2020-03-29T22:00:00.000Z") },
  { sumValue: 489680, ts: ISODate("2020-03-30T22:00:00.000Z") }
]

As you can see from the result, the change in timezone offset is not considered by the aggregation function as we were expecting.

From my understanding, using dateTrunc with unit hour and timezone, it will create buckets starting from the reference date (1/1/2020 in our case) considering the timezone offset that applies for the reference date, and it will not change the buckets when the timezone offset changes during the year.

Is this a bug? And if not, what is the explanation for the different results when the unit is day or hours?

1 Like