Back to DocumentationMQL Documentation

$dateTrunc

Expression operator truncates a date to the nearest specified unit (for example, hour, day, month). It's useful when working with time-series data or when grouping data by specific time intervals. This operator can be used to simplify and standardize date calculations.

Syntax

$dateTrunc: { date: <dateExpression>, unit: <unit>, binSize: <number>, timezone: <timezone>, startOfWeek: <day> }

Parameters

dateobjectrequired

The date to truncate.

unitstringrequired

The unit to truncate the date to. Supported values include year, month, week, day, hour, minute, second, and millisecond.

binSizenumber

Optional. The size of each bin for truncation. For example, if binSize is 2 and unit is hour, the date is truncated to every 2 hours.

timezonestring

Optional. The timezone to use for truncation. Defaults to UTC if not specified.

startOfWeekstring

Optional. Used when unit is "week".

Examples

Truncate to the day

This query uses $dateTrunc to truncate the lastUpdated timestamp to the start of the day (00:00:00) in UTC. The operator is useful for grouping or comparing data by calendar day regardless of time.

Query:

db.stores.aggregate([
  {
    $match: { _id: "e6410bb3-843d-4fa6-8c70-7472925f6d0a" }
  },
  {
    $project: {
      _id: 0,
      truncatedToDay: {
        $dateTrunc: {
          date: "$lastUpdated",
          unit: "day"
        }
      }
    }
  }
])

Output:

[
  {
    "truncatedToDay": "2024-11-29T00:00:00.000Z"
  }
]

Truncate to the start of the week

This query uses $dateTrunc to round the lastUpdated timestamp down to the start of its week. It specifies Monday as the start of the week to ensure consistent calendar alignment.

Query:

db.stores.aggregate([
  {
    $match: { _id: "e6410bb3-843d-4fa6-8c70-7472925f6d0a" }
  },
  {
    $project: {
      _id: 0,
      truncatedToWeek: {
        $dateTrunc: {
          date: "$lastUpdated",
          unit: "week",
          startOfWeek: "Monday"
        }
      }
    }
  }
])

Output:

[
  {
    "truncatedToWeek": "2024-11-25T00:00:00.000Z"
  }
]

Related