You are viewing an offline version of MongoDB documentation. Some page features might be unavailable. To view the latest version of the page or use interactive features, visit the live page.

Database Manual

Aggregation Operations

Reference

Operators

$dateDiff (aggregation)

Definition

$dateDiff

New in version 5.0.

Returns the difference between two dates.

The $dateDiff expression has this syntax:

{
   $dateDiff: {
      startDate: <Expression>,
      endDate: <Expression>,
      unit: <Expression>,
      timezone: <tzExpression>,
      startOfWeek: <String>
   }
}

Subtracts startDate from endDate. Returns an integer in the specified unit.

Field

Required/Optional

Description

startDate

Required

The start of the time period. The startDate can be any expression that resolves to a Date, a Timestamp, or an ObjectID.

endDate

Required

The end of the time period. The endDate can be any expression that resolves to a Date, a Timestamp, or an ObjectID.

unit

Required

The time measurement unit between the startDate and endDate. It is an expression that resolves to a string:

year
quarter
week
month
day
hour
minute
second
millisecond

timezone

Optional

The timezone to carry out the operation. <tzExpression> must be a valid expression that resolves to a string formatted as either an Olson Timezone Identifier or a UTC Offset. If no timezone is provided, the result is displayed in UTC.

Format

Examples

Olson Timezone Identifier

"America/New_York"
"Europe/London"
"GMT"

UTC Offset

+/-[hh]:[mm], e.g. "+04:45"
+/-[hh][mm], e.g. "-0530"
+/-[hh], e.g. "+03"

startOfWeek

Optional

Used when the unit is equal to week. Defaults to Sunday. The startOfWeek parameter is an expression that resolves to a case insensitive string:

monday (or mon)
tuesday (or tue)
wednesday (or wed)
thursday (or thu)
friday (or fri)
saturday (or sat)
sunday (or sun)

Behavior

No Fractional Units

The $dateDiff expression returns the integer difference between the startDate and endDate measured in the specified units. Durations are measured by counting the number of times a unit boundary is passed. For example, two dates that are 18 months apart would return 1 year difference instead of 1.5 years.

Start Of Week

The start of the week is Sunday unless modified by the startOfWeek parameter. Any week that begins between the startDate and endDate on the specified day will be counted. The week count is not bounded by calendar month or calendar year.

Time Zone

When using an Olson Timezone Identifier in the <timezone> field, MongoDB applies the DST offset if applicable for the specified timezone.

For example, consider a sales collection with the following document:

{
   "_id" : 1,
   "item" : "abc",
   "price" : 20,
   "quantity" : 5,
   "date" : ISODate("2017-05-20T10:24:51.303Z")
}

The following aggregation illustrates how MongoDB handles the DST offset for the Olson Timezone Identifier. The example uses the $hour and $minute operators to return the corresponding portions of the date field:

db.sales.aggregate([
{
   $project: {
      "nycHour": {
         $hour: { date: "$date", timezone: "-05:00" }
       },
       "nycMinute": {
          $minute: { date: "$date", timezone: "-05:00" }
       },
       "gmtHour": {
          $hour: { date: "$date", timezone: "GMT" }
       },
       "gmtMinute": {
          $minute: { date: "$date", timezone: "GMT" } },
       "nycOlsonHour": {
          $hour: { date: "$date", timezone: "America/New_York" }
       },
       "nycOlsonMinute": {
          $minute: { date: "$date", timezone: "America/New_York" }
       }
   }
}])

The operation returns the following result:

{
   "_id": 1,
   "nycHour" : 5,
   "nycMinute" : 24,
   "gmtHour" : 10,
   "gmtMinute" : 24,
   "nycOlsonHour" : 6,
   "nycOlsonMinute" : 24
}

Additional Details

The algorithm calculates the date difference using the Gregorian calendar.

Leap years and daylight savings time are accounted for but not leap seconds.

The difference returned can be negative.

Examples

Elapsed Time

Create a collection of customer orders:

db.orders.insertMany(
   [
      {
         custId: 456,
         purchased: ISODate("2020-12-31"),
         delivered: ISODate("2021-01-05")
      },
      {
         custId: 457,
         purchased: ISODate("2021-02-28"),
         delivered: ISODate("2021-03-07")
      },
      {
         custId: 458,
         purchased: ISODate("2021-02-16"),
         delivered: ISODate("2021-02-18")
      }
   ]
)

The following example:

Returns the average number of days for a delivery.
Uses dateDiff to calculate the difference between the purchased date and the delivered date.

 db.orders.aggregate(
    [
       {
          $group:
             {
                 _id: null,
                 averageTime:
                    {
                       $avg:
                          {
                             $dateDiff:
                                {
                                    startDate: "$purchased",
                                    endDate: "$delivered",
                                    unit: "day"
                                }
                           }
                    }
             }
       },
       {
          $project:
             {
                _id: 0,
                numDays:
                   {
                      $trunc:
                         [ "$averageTime", 1 ]
                   }
             }
        }
    ]
)

The $avg accumulator in the $group stage uses $dateDiff on each document to get the time between the purchased and delivered dates. The resulting value is returned as averageTime.

The decimal portion of the averageTime is truncated ($trunc) in the $project stage to produce output like this:

{ "numDays" : 4.6 }

Result Precision

Create this collection with starting and ending dates for a subscription.

db.subscriptions.insertMany(
   [
      {
         custId: 456,
         start: ISODate("2010-01-01"),
         end: ISODate("2011-01-01")
      },
      {
         custId: 457,
         start: ISODate("2010-01-01"),
         end: ISODate("2011-06-31")
      },
      {
         custId: 458,
         start: ISODate("2010-03-01"),
         end: ISODate("2010-04-30")
      }
   ]
)

The $dateDiff expression returns a time difference expressed in integer units. There are no fractional parts of a unit. For example, when counting in years there are no half years.

In this example, note how changing the unit changes the returned precision:

db.subscriptions.aggregate(
   [
      {
         $project:
            {
               Start: "$start",
               End: "$end",
               years:
                  {
                     $dateDiff:
                        {
                           startDate: "$start",
                           endDate: "$end",
                           unit: "year"
                        }
                  },
               months:
                  {
                     $dateDiff:
                        {
                           startDate: "$start",
                           endDate: "$end",
                           unit: "month"
                        }
                  },
               days:
                  {
                     $dateDiff:
                        {
                           startDate: "$start",
                           endDate: "$end",
                           unit: "day"
                        }
                  },
               _id: 0
            }
       }
   ]
 )

The results are summarized in this table:

Start	End	Years	Months	Days
2010-01-01	2011-01-01	1	12	365
2010-01-01	2011-07-01	1	18	546
2010-03-01	2010-04-30	0	1	60

The count only increments when a new unit starts, so 18 months are reported as 1 year in the second row and 60 days are reported as one month in the third row.

Weeks Per Month

Create a collection of months:

db.months.insertMany(
     [
        {
           month: "January",
           start: ISODate("2021-01-01"),
           end: ISODate("2021-01-31")
        },
        {
           month: "February",
           start: ISODate("2021-02-01"),
           end: ISODate("2021-02-28")
        },
        {
           month: "March",
           start: ISODate("2021-03-01"),
           end: ISODate("2021-03-31")
        },
     ]
   )

You can change the start of each week, and count the resulting number of weeks in each month with the following code:

db.months.aggregate(
   [
      {
         $project:
            {
               wks_default:
                  {
                     $dateDiff:
                        {
                           startDate: "$start",
                           endDate: "$end",
                           unit: "week"
                        }
                  },
               wks_monday:
                  {
                     $dateDiff:
                        {
                           startDate: "$start",
                           endDate: "$end",
                           unit: "week",
                           startOfWeek: "Monday"
                        }
                  },
               wks_friday:
                  {
                     $dateDiff:
                        {
                           startDate: "$start",
                           endDate: "$end",
                           unit: "week",
                           startOfWeek: "fri"
                        }
                  },
               _id: 0
            }
       }
   ]
 )

The results are summarized in this table:

Month	Sunday	Monday	Friday
January	5	4	4
February	4	3	4
March	4	4	4

From the results:

When the startOfWeek is Sunday, the 5th week in January, 2021 begins on the 31st.
Because the 31st is a Sunday and it is between startDate and endDate, one week is added to the count.
The week count is incremented even when a calendar week finishes after endDate or in the next calendar period.

Back

$dateAdd

$dateFromParts