$dateSubtract (aggregation operator)（聚合运算符）

Time Measurement时间测量

MongoDB follows prevaling database usage and works with time in UTC. The dateSubtract expression always takes a startDate in UTC and returns a result in UTC. If the timezone is specified, the calculation will be done using the specified timezone. The timezone is especially important when a calculation involves Daylight Savings Time (DST).MongoDB遵循以前的数据库使用情况，并使用UTC时间。dateSubject表达式始终采用UTC格式的startDate，并返回UTC格式的结果。如果指定了timezone，则将使用指定的时区进行计算。当计算涉及夏令时（DST）时，时区尤为重要。

If the unit is a month, or larger the operation adjusts to account for the last day of the month. Subtracting one month on the last day of March, for example, demonstrates the "last-day-of-the-month" adjustment.如果unit为一month或更大，则操作将调整为考虑该月的最后一天。例如，在3月的最后一天减去一个月，即表示“本月最后一天”的调整。

{
   $dateSubtract:
      {
         startDate: ISODate("2021-03-31T12:10:05Z"),
         unit: "month",
         amount: 1
      }
}

Notice that the date returned, ISODate("2021-02-28T12:10:05Z"), is the 28th and not the 31st since February has fewer days than March.请注意，返回的日期ISODate("2021-02-28T12:10:05Z")是28日，而不是31日，因为2月的天数比3月少。

Time Zone时区

When using an Olson Timezone Identifier in the <timezone> field, MongoDB applies the DST offset if applicable for the specified timezone.在<Timezone>字段中使用奥尔森时区标识符时，MongoDB会应用DST偏移（如果适用于指定的时区）。

For example, consider a sales collection with the following document:例如，考虑具有以下文档的sales集合：

 db.sales.insertOne(
  {
   "_id" : 1,
   "item" : "abc",
   "price" : 10,
   "quantity" : 2,
   "date" : ISODate("2014-01-01T08:15:39.736Z")
  }
)

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:以下聚合说明了MongoDB如何处理Olson时区标识符的DST偏移。该示例使用$hour和$minute运算符返回日期字段的相应部分：

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
}

Subtract A Fixed Amount减去固定金额

Consider a collection of system connection times like these:考虑以下系统连接时间的集合：

db.connectionTime.insertMany(
  [
     {
        custId: 457,
        login: ISODate("2020-12-25T19:04:00"),
        logout: ISODate("2020-12-28T09:04:00")
     },
     {
        custId: 457,
        login: ISODate("2021-01-27T05:12:00"),
        logout: ISODate("2021-01-28T13:05:00")
     },
     {
        custId: 458,
        login: ISODate("2021-01-22T06:27:00"),
        logout: ISODate("2021-01-31T11:00:00")
     },
     {
        custId: 459,
        login: ISODate("2021-02-14T20:14:00"),
        logout: ISODate("2021-02-17T16:05:00")
     },
     {
        custId: 460,
        login: ISODate("2021-02-26T02:44:00"),
        logout: ISODate("2021-02-18T14:13:00")
        }
  ]
)

Due to a service issue you need to subtract 3 hours from each of the January 2021 logout times. You can use $dateSubtract in an aggregation pipeline to decrement the logoutTime.由于服务问题，您需要从2021年1月的每次注销时间中减去3小时。您可以在聚合管道中使用$dateSubject来减少logoutTime。

db.connectionTime.aggregate(
   [
      {
         $match:
            {
               $expr:
                  {
                     $eq:
                        [
                            { $year: "$logout" },
                              2021
                        ]
                  },
               $expr:
                  {
                     $eq:
                        [
                            { $month: "$logout" },
                              1
                        ]
                   }
             }
       },
       {
          $project:
             {
                logoutTime:
                   {
                      $dateSubtract:
                         {
                            startDate: "$logout",
                            unit: "hour",
                            amount: 3
                         }
                   }
              }
        },
        {
           $merge: "connectionTime"
        }
   ]
)

Two similar comparisons are made in the $match stage. 在$match阶段进行了两次类似的比较。First the $year and $month operators extract the year and month, respectively, from the logoutTime Date object. 首先，$year和$month运算符分别从logoutTime Date对象中提取年和月。Then the month and year are checked to see if they match the selection targets. Since "January" is encoded as "1", $expr is true when the year and month are equal ($eq) to "2021" and "1".然后检查月份和年份是否与选择目标匹配。由于“January”被编码为“1”，因此当年和月等于（$eq）“2021”和“1”时，$expr为true。

The $project stage uses $dateSubtract to subtract 3 hours from the logoutTime of each selected dcoument.$project阶段使用$dateSubject从每个选定数据文档的注销时间中减去3小时。

Finaly, the $merge stage updates the collection, writing the new logoutTime for the modified documents.最后，$merge阶段更新集合，为修改后的文档写入新的logoutTime。

The resulting documents look like this:生成的文档如下：

{
 "_id" : ObjectId("603dd94b044b995ad331c0b5"),
 "custId" : 457,
 "login" : ISODate("2020-12-25T19:04:00Z"),
 "logout" : ISODate("2020-12-28T09:04:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b6"),
 "custId" : 457,
 "login" : ISODate("2021-01-27T05:12:00Z"),
 "logout" : ISODate("2021-01-28T13:05:00Z"),
 "logoutTime" : ISODate("2021-01-28T10:05:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b7"),
 "custId" : 458,
 "login" : ISODate("2021-01-22T06:27:00Z"),
 "logout" : ISODate("2021-01-31T11:00:00Z"),
 "logoutTime" : ISODate("2021-01-31T08:00:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b8"),
 "custId" : 459,
 "login" : ISODate("2021-02-14T20:14:00Z"),
 "logout" : ISODate("2021-02-17T16:05:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b9"),
 "custId" : 460,
 "login" : ISODate("2021-02-26T02:44:00Z"),
 "logout" : ISODate("2021-02-18T14:13:00Z")
}

Filter by Relative Dates按相对日期筛选

You want to send a survey to clients who have used your service in the past week. The $dateSubtract expression can create a range filter relative to the time the query is executed.您想向过去一周使用过您服务的客户发送一份调查。$dateSubject表达式可以创建相对于查询执行时间的范围筛选器。

db.connectionTime.aggregate(
   [
      {
         $match:
            {
               $expr:
                  {
                     $gt:
                        [
                           "$logoutTime",
                            {
                               $dateSubtract:
                                  {
                                     startDate: "$$NOW",
                                     unit: "week",
                                     amount: 1
                                  }
                            }
                        ]
                  }
             }
      },
      {
         $project:
            {
               _id: 0,
               custId: 1,
               loggedOut:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d",
                           date: "$logoutTime"
                        }
                  }
            }
      }
   ]
)

The built in aggregation variable $$NOW returns the current datetime in ISODate format. 内置聚合变量$$NOW以ISODate格式返回当前日期时间。The $match stage uses the value in $$NOW to get today's date. $match阶段使用$$NOW中的值来获取今天的日期。Then the comparison expression ($expr) filters the collection using greater than ($gt) and $dateSubtract to match documents that have a logoutTime in the past week.然后，比较表达式（$expr）使用大于（$gt）和$dateSubject筛选集合，以匹配过去一周有logoutTime的文档。

The $project stage uses the $dateToString expression to convert the dates to a more readable format. $project阶段使用$dateToString表达式将日期转换为更可读的格式。Without the conversion, MongoDB returns the date in ISODate format and assumes a UTC timezone.如果不进行转换，MongoDB将以ISODate格式返回日期，并假定为UTC时区。

The output shows two customers have logged out in the last week.输出显示上周有两名客户注销。

{ "custId" : 459, "loggedOut" : "2021-02-17" }
{ "custId" : 460, "loggedOut" : "2021-02-18" }

Adjust for Daylight Savings Time调整夏令时

All dates are stored internally in UTC time. When a timezone is specified, $dateSubtract uses local time to carry out the calculations. The results are displayed in UTC.所有日期都以UTC时间存储在内部。当指定timezone时，$dateSubject使用本地时间进行计算。结果以UTC显示。

You have customers in several timezones and you want to see what effect daylight savings time might have on your billing periods if you bill by day or by hour.您有多个时区的客户，您想看看如果按day或按hour计费，夏令时对计费周期会有什么影响。

Create this collection of connection times:创建此连接时间集合：

db.billing.insertMany(
   [
      {
         location: "America/New_York",
         login: ISODate("2021-03-14T10:00:00-0500"),
         logout: ISODate("2021-03-14T18:00:00-0500")
      },
      {
         location: "America/Mexico_City",
         login: ISODate("2021-03-14T10:00:00-00:00"),
         logout: ISODate("2021-03-15T08:00:00-0500")
      }
   ]
)

First subtract 1 day, then subtract 24 hours from the login dates in each document.首先从每个文档中的login日期中减去1天，然后再减去24小时。

db.billing.aggregate(
   [
      {
         $project:
            {
               _id: 0,
               location: 1,
               start:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date: "$login"
                        }
                  },
               days:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                    {
                                       startDate: "$login",
                                       unit: "day",
                                       amount: 1,
                                       timezone: "$location"
                                    }
                              }
                        }
                  },
               hours:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                 {
                                    startDate: "$login",
                                    unit: "hour",
                                    amount: 24,
                                    timezone: "$location"
                                 }
                              }
                        }
                  },
               startTZInfo:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date: "$login",
                           timezone: "$location"
                        }
                  },
               daysTZInfo:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                    {
                                       startDate: "$login",
                                       unit: "day",
                                       amount: 1,
                                       timezone: "$location"
                                    }
                              },
                           timezone: "$location"
                        }
                  },
               hoursTZInfo:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                    {
                                       startDate: "$login",
                                       unit: "hour",
                                       amount: 24,
                                       timezone: "$location"
                                    }
                              },
                           timezone: "$location"
                        }
                  },
            }
      }
   ]
).pretty()

The $dateToString expression reformats the output for readability. Results are summarized here:$dateToString表达式重新格式化输出以提高可读性。结果总结如下：

The chart highlights several points:该图表突出了几个要点：

• Unformatted dates are returned in UTC. The $login for New York is UTC -5, however the start, days, and hours rows display the time in UTC.未格式化的日期以UTC返回。纽约的$login是UTC-5，但start、days和hours行显示的是UTC时间。
• March 14th is the start of DST in New York, but not in Mexico. The calculated time is adjusted when a location switches to DST and crosses from one day to the next.3月14日是纽约夏令时的开始，但墨西哥不是。当一个地点切换到夏令时并从一day跨越到下一天时，计算的时间会进行调整。
• DST modifies the length of the day, not the hour. There is no DST change for hours. 夏令时修改的是一day的长度，而不是hour。几个hour没有夏令时变化。There is an only an adjustment for DST when the measurement unit is day or larger and the computation crosses a clock change in the specified timezone.只有当测量unit为day或更大，并且计算跨越指定timezone的时钟变化时，才会对夏令时进行调整。