$dateSubtract (aggregation)

Time Measurement时间测量

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

If the unit is a month, or larger the operation adjusts to account for the last day of the month. 如果unit为一month或更大，则操作将调整以考虑该月的最后一天。Subtracting one month on the last day of March, for example, demonstrates the "last-day-of-the-month" adjustment.例如，在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集合：

{
   "_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. 以下聚合说明了MongoDB如何处理奥尔森时区标识符的DST偏移。The example uses the $hour and $minute operators to return the corresponding portions of the date field:示例使用$hour和$minute运算符返回date字段的相应部分：

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. 由于服务问题，您需要从每个2021年1月注销时间减去3小时。You can use $dateSubtract in an aggregation pipeline to decrement the logoutTime.您可以在聚合管道中使用$dateSubtract来减少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日期对象中提取年份和月份。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".由于“一月”被编码为“1”，所以当年和月等于（$eq）到“2021”和“1”时，$expr为true。

The $project stage uses $dateSubtract to subtract 3 hours from the logoutTime of each selected dcoument.$project阶段使用$dateSubtract从每个选定数据的logoutTime中减去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.$dateSubtract表达式可以创建一个相对于查询执行时间的范围筛选器。

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）和$dateSubtract筛选集合，以匹配在过去一周中有logoutTime的文档。

The $project stage uses the $dateToString expression to convert the dates to a more readable format. $project阶段使用$dateToString表达式将日期转换为更可读的格式。Without the conversion MongoDB would return the date in ISODate format. 如果没有转换，MongoDB将以ISODate格式返回日期。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. 所有日期都以UTC时间的形式存储。When a timezone is specified, $dateSubtract uses local time to carry out the calculations. 当指定timezone时，$dateSubtract使用本地时间执行计算。The results are displayed in UTC.结果以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.你有几个时区的客户，如果你按天或按小时计费，你想看看夏令时对你的计费周期有什么影响。

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. $dateToString表达式重新设置输出的可读性。Results are summarized here:结果总结如下：

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

• Unformatted dates are returned in UTC. 未格式化的日期以UTC格式返回。The $login for New York is UTC -5, however the start, days, and hours rows display the time in UTC.纽约的$login是UTC-5，但是start、days和hours行以UTC显示时间。
• March 14th is the start of DST in New York, but not in Mexico. 3月14日是纽约DST的开始，但不是墨西哥DST的开始。The calculated time is adjusted when a location switches to DST and crosses from one day to the next.当一个位置切换到DST并从一天切换到下一天时，计算的时间会进行调整。
• DST modifies the length of the day, not the hour. DST修改的是一天的长度，而不是小时的长度。There is no DST change for hours. hours内没有DST变化。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或更大且计算跨越指定时区内的时钟变化时，仅对DST进行调整。