MongoDB 计算时间戳差值 (小时)：Aggregation $subtract $divide

本文演示如何使用 MongoDB Aggregation Pipeline 计算两个 DateTime 字段的差值，并转换为小时单位。

创建示例数据

> db.timestampDifferenceDemo.insertOne({
  "MovieBeginningTime": new ISODate("2019-05-12 10:20:30"),
  "MovieEndingTime": new ISODate("2019-05-12 12:30:20")
});
{ "acknowledged" : true, "insertedId" : ObjectId("5cd7ba1f6d78f205348bc644") }
> db.timestampDifferenceDemo.insertOne({
  "MovieBeginningTime": new ISODate("2019-05-12 04:00:00"),
  "MovieEndingTime": new ISODate("2019-05-12 07:10:00")
});
{ "acknowledged" : true, "insertedId" : ObjectId("5cd7ba3b6d78f205348bc645") }

显示原始数据

> db.timestampDifferenceDemo.find().pretty();

{ "_id" : ObjectId("5cd7ba1f6d78f205348bc644"), "MovieBeginningTime" : ISODate("2019-05-12T10:20:30Z"), "MovieEndingTime" : ISODate("2019-05-12T12:30:20Z") }
{ "_id" : ObjectId("5cd7ba3b6d78f205348bc645"), "MovieBeginningTime" : ISODate("2019-05-12T04:00:00Z"), "MovieEndingTime" : ISODate("2019-05-12T07:10:00Z") }

Aggregation 计算小时差

> db.timestampDifferenceDemo.aggregate([
  { $project: {
    DifferenceInHours: { 
      $divide: [ 
        { $subtract: ["$MovieEndingTime", "$MovieBeginningTime"] }, 
        3600000 
      ] 
    }
  }}
]);

结果

{ "_id" : ObjectId("5cd7ba1f6d78f205348bc644"), "DifferenceInHours" : 2.1638888888888888 }
{ "_id" : ObjectId("5cd7ba3b6d78f205348bc645"), "DifferenceInHours" : 3.1666666666666665 }

计算原理

$subtract: [end, start] - 两个 ISODate 相减，结果为 毫秒数 (Long)
$divide: [milliseconds, 3600000] - 毫秒除以 3,600,000 (1000ms × 60s × 60min) 得到小时
结果为浮点数，包含小数部分 (分钟/秒折算)

常用时间单位换算常数

单位	毫秒数	公式
毫秒	1	-
秒	1,000	1000
分钟	60,000	1000 × 60
小时	3,600,000	1000 × 60 × 60
天	86,400,000	1000 × 60 × 60 × 24
周	604,800,000	1000 × 60 × 60 × 24 × 7

进阶：只取整小时 / 向上取整

db.timestampDifferenceDemo.aggregate([
  { $project: {
    diffMs: { $subtract: ["$MovieEndingTime", "$MovieBeginningTime"] },
    diffHours: { $divide: [{ $subtract: ["$MovieEndingTime", "$MovieBeginningTime"] }, 3600000] },
    diffHoursFloor: { $floor: { $divide: [{ $subtract: ["$MovieEndingTime", "$MovieBeginningTime"] }, 3600000] } },
    diffHoursCeil: { $ceil: { $divide: [{ $subtract: ["$MovieEndingTime", "$MovieBeginningTime"] }, 3600000] } }
  }}
]);

时区考量

ISODate 存储为 UTC
$subtract 直接计算 UTC 时间差，不受时区影响
若需按本地日历日计算，需先用 $dateToString 转时区再解析

替代方案：应用层计算

对于简单场景，在应用代码中计算通常更简单高效：

// Node.js
const diffHours = (end.getTime() - start.getTime()) / 3600000;

// Python
diff_hours = (end - start).total_seconds() / 3600