$dateTrunc aggregation operator doesn't return expected value

Liam_Tat_Tze_Tey · 2024-12-23T12:49:53.022Z

I was using $dateTrunc on a timeseries collection to group the timeseries data from 2024-12-01 to 2024-12-07 into a single bin.

Screenshot 2024-12-22 at 22.40.385088×1296 316 KB

Here is the aggregate pipeline:

[
  {
    $match: {
      timestamp: {
        $gte: ISODate('2024-12-01'),
        $lte: ISODate('2024-12-07'),
      },
    }
  },
  {
    $group: {
      _id: {
        timestamp: {
          $dateTrunc: {
            date: "$timestamp",
            unit: 'day',
            binSize: 7,
          },
        }
      },
      count: {
      	$sum: 1
      }
    }
  },
  {
    $set: {
      timestamp: "$_id.timestamp"
    }
  },
  {
    $sort: {
      timestamp: 1
    }
  }
]

Here is the result:

[
  {
    "_id": {
      "timestamp": {
        "$date": "2024-11-30T00:00:00.000Z"
      }
    },
    "count": 719,
    "timestamp": {
      "$date": "2024-11-30T00:00:00.000Z"
    }
  },
  {
    "_id": {
      "timestamp": {
        "$date": "2024-12-07T00:00:00.000Z"
      }
    },
    "count": 116,
    "timestamp": {
      "$date": "2024-12-07T00:00:00.000Z"
    }
  }
]

I expect the aggregation pipeline to return a single data point (from 2024-12-01 to 2024-12-07). However, the result is not what I expected.

It would be great if anyone could provide guidance on this issue. Thank you in advance!

chris · 2024-12-25T16:24:03.748Z

Hi @Liam_Tat_Tze_Tey

The time periods start at a reference date, which is determind by unit. If unit is:

A string other than week, $dateTrunc uses a reference date of 2000-01-01T00:00:00.00Z. For example, if binSize is 10 and unit is year, example time periods are: …

Accordingly the 7 day bins for the match selection are 2024-11-30 and 2024-12-07. 2024-12-07T00:00:00.00Z is the start of the next bin.

Would other units such as week or month be more suitable in this case ?
binSize: 7 and unit: 'day' : Mongo playground
unit:'month' and implicit binSize:1: Mongo playground
unit:'week' and implicit binSize:1: Mongo playground

Liam_Tat_Tze_Tey · 2024-12-28T13:27:35.126Z

Here is the stack overflow discussion of this issue - MongoDB $dateTrunc aggregation operator doesn't return expected value - Stack Overflow. Seems the starting point for unit: day with binSize are random

chris · 2024-12-28T18:20:50.065Z

Liam_Tat_Tze_Tey:

Seems the starting point for unit: day with binSize are random

Its not. Per the docs of $dateTrunc quoted in my previous reply(and the last post on the SO thread) the offset is from 2000-01-01.

Calculate it forward and the bin for the 7 day bin begins 2004-11-30.

#!/usr/bin/env python3
from datetime import datetime,timedelta

base_date=datetime(2000,1,1)
days1=timedelta(days=1)
date=datetime(2024,11,1)
while date < datetime(2025,1,1):
    if (date-base_date).days % 7 == 0:
        print(date)
    date+=days1