$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月的最后一天减去一个月（month），表示“一个月的最后几天”的调整。

{
   $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>字段中使用Olson时区标识符时，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如何处理Olson时区标识符的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. You can use $dateSubtract in an aggregation pipeline to decrement the logoutTime.由于服务问题，您需要从2021年1月的每次注销时间中减去3小时。您可以在聚合管道中使用$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运算符分别从logoutTimeDate对象中提取年份和月份。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为真。

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. The results are displayed in UTC.如果指定了timezone，$dateSubtract将使用本地时间进行计算。结果以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. 未格式化的日期以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日是纽约夏令时的开始，但墨西哥没有。The calculated time is adjusted when a location switches to DST and crosses from one day to the next.当位置切换到夏令时并从一天穿越到下一天时，计算的时间会进行调整。
• DST modifies the length of the day, not the hour. 夏令时修改的是一天的长度，而不是小时的长度。There is no DST change for hours. 夏令时在数小时内没有变化。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的时钟变化时，仅对DST进行调整。