Docs Menu

$dateTrunc (aggregation)

On this page

  • Definition
  • Behavior
  • Examples
$dateTrunc

New in version 5.0.

Truncates a date.

$dateTrunc syntax:

{
$dateTrunc: {
date: <Expression>,
unit: <Expression>,
binSize: <Expression>,
timezone: <tzExpression>,
startOfWeek: <Expression>
}
}
Field
Required / Optional
Description
Required

The date to truncate, specified in UTC. The date can be any expression that resolves to a Date, a Timestamp, or an ObjectID.

Required

The unit of time, specified as an expression that must resolve to one of these strings:

  • year
  • quarter
  • week
  • month
  • day
  • hour
  • minute
  • second

Together, binSize and unit specify the time period used in the $dateTrunc calculation.

Optional

The numeric time value, specified as an expression that must resolve to a positive non-zero number. Defaults to 1.

Together, binSize and unit specify the time period used in the $dateTrunc calculation.

Optional

The timezone for the $dateTrunc calculation, specified as an expression that must resolve to a string that contains one of these values:

If no timezone is provided, the $dateTrunc calculation is performed in UTC.

Format
Examples
Olson Timezone Identifier
  • America/New_York
  • Europe/London
  • GMT
UTC Offset
  • +/-[hh]:[mm] (example, +04:45)
  • +/-[hh][mm] (example, -0530)
  • +/-[hh] (example, +03)
Optional

The start of the week. Used when unit is week. Defaults to Sunday.

startOfWeek is an expression that must resolve to one of these case insensitive strings:

  • monday (or mon)
  • tuesday (or tue)
  • wednesday (or wed)
  • thursday (or thu)
  • friday (or fri)
  • saturday (or sat)
  • sunday (or sun)
Tip
See also:

$dateTrunc:

Together, binSize and unit specify the time period used in the $dateTrunc calculation.

For example:

  • If binSize is 1 and unit is hours, the time period is one hour. For the date 2021-03-20T11:30:05Z, $dateTrunc returns 2021-03-20T11:00:00Z.
  • If binSize is 2 and unit is hours, the time period is two hours. For the date 2021-03-20T11:30:05Z, $dateTrunc returns 2021-03-20T10:00:00Z.

$dateTrunc:

  • Divides the time for the $dateTrunc calculation into binSize time periods in the specified time unit.

    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:

      • 2000-01-01T00:00:00.00Z
      • 2010-01-01T00:00:00.00Z
      • 2020-01-01T00:00:00.00Z
    • Equal to week, $dateTrunc uses a reference date that is set to the earliest first day of the week that is greater than or equal to 2000-01-01. The first day is set using startOfWeek (the default is Sunday).
  • Returns the lower boundary of the time period that the date is in. The boundary is returned as an ISODate. If the binSize field is 1, $dateTrunc sets the least significant parts (as determined by unit) of the returned ISODate to 0 and keeps the rest of the ISODate the same.

If unit is:

If unit is:

Create a cakeSales collection that contains cake sales in the states of California (CA) and Washington (WA):

db.cakeSales.insertMany( [
{ _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"),
state: "CA", price: 13, quantity: 120 },
{ _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"),
state: "WA", price: 14, quantity: 140 },
{ _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"),
state: "CA", price: 12, quantity: 145 },
{ _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"),
state: "WA", price: 13, quantity: 104 },
{ _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"),
state: "CA", price: 41, quantity: 162 },
{ _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"),
state: "WA", price: 43, quantity: 134 }
] )

The cakeSales collection is used in the following examples.

This example uses $dateTrunc in a $project stage to truncate the cake sales orderDate values to two weeks:

db.cakeSales.aggregate( [
{
$project: {
_id: 1,
orderDate: 1,
truncatedOrderDate: {
$dateTrunc: {
date: "$orderDate", unit: "week", binSize: 2,
timezone: "America/Los_Angeles", startOfWeek: "Monday"
}
}
}
}
] )

In the example:

  • $project includes the _id, orderDate, and truncatedOrderDate fields in the output.
  • $dateTrunc truncates the orderDate field to a 2 binSize week unit time period in the America/Los_Angeles timezone with startOfWeek set to Monday.

In this example output, the truncated orderDate is shown in the truncatedOrderDate field:

[
{
_id: 0,
orderDate: ISODate("2020-05-18T14:10:30.000Z"),
truncatedOrderDate: ISODate("2020-05-11T07:00:00.000Z")
},
{
_id: 1,
orderDate: ISODate("2021-03-20T11:30:05.000Z"),
truncatedOrderDate: ISODate("2021-03-15T07:00:00.000Z")
},
{
_id: 2,
orderDate: ISODate("2021-01-11T06:31:15.000Z"),
truncatedOrderDate: ISODate("2021-01-04T08:00:00.000Z")
},
{
_id: 3,
orderDate: ISODate("2020-02-08T13:13:23.000Z"),
truncatedOrderDate: ISODate("2020-02-03T08:00:00.000Z")
},
{
_id: 4,
orderDate: ISODate("2019-05-18T16:09:01.000Z"),
truncatedOrderDate: ISODate("2019-05-13T07:00:00.000Z")
},
{
_id: 5,
orderDate: ISODate("2019-01-08T06:12:03.000Z"),
truncatedOrderDate: ISODate("2019-01-07T08:00:00.000Z")
}
]

This example uses $dateTrunc in a $group stage to truncate the cake sales orderDate values to six months and return the sum of the quantity values:

db.cakeSales.aggregate( [
{
$group: {
_id: {
truncatedOrderDate: {
$dateTrunc: {
date: "$orderDate", unit: "month", binSize: 6
}
}
},
sumQuantity: { $sum: "$quantity" }
}
}
] )

In the example:

  • $group has the _id field set to the truncatedOrderDate field to group the cakeSales documents, and returns the sum of the quantity values for each group using $sum.
  • $dateTrunc truncates the orderDate field to a 6 binSize month unit time period.

In this example output, the truncated orderDate is shown in the truncatedOrderDate field and the quantity sum is shown in the sumQuantity field:

[
{
_id: { truncatedOrderDate: ISODate("2020-01-01T00:00:00.000Z") },
sumQuantity: 224
},
{
_id: { truncatedOrderDate: ISODate("2021-01-01T00:00:00.000Z") },
sumQuantity: 285
},
{
_id: { truncatedOrderDate: ISODate("2019-01-01T00:00:00.000Z") },
sumQuantity: 296
}
]
Give Feedback
© 2021 MongoDB, Inc.

About

  • Careers
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.