在Elasticsearch中基于脚本对两个日期字段的差值进行排序

MongoDB 实现方式 #

方法1：聚合管道计算差值后排序 #

通过$subtract计算两个日期字段的毫秒级差值，再基于该差值排序。升序表示更新时间离创建时间更近的文档在前，降序则相反。

db.collection.aggregate([
  {
    $addFields: {
      updateDelay: { $subtract: ["$updatedAt", "$createdAt"] }
    }
  },
  {
    $sort: { updateDelay: 1 } // 1为升序，-1为降序
  }
])

若不需要保留计算出的updateDelay字段，可在排序后通过$project移除：

db.collection.aggregate([
  { $addFields: { updateDelay: { $subtract: ["$updatedAt", "$createdAt"] } } },
  { $sort: { updateDelay: 1 } },
  { $project: { updateDelay: 0 } }
])

方法2：排序阶段直接使用表达式（MongoDB 5.0+支持） #

MongoDB 5.0及以上版本允许在$sort阶段直接使用聚合表达式，无需额外添加字段：

db.collection.aggregate([
  {
    $sort: {
      $subtract: ["$updatedAt", "$createdAt"]: 1
    }
  }
])

SQL 实现方式 #

不同关系型数据库的日期差值函数略有差异，以下是常见数据库的实现示例：

MySQL #

使用TIMESTAMPDIFF指定差值单位（如秒、分钟），或通过时间戳转换计算毫秒差值：

-- 按秒级差值升序排序
SELECT *
FROM your_table
ORDER BY TIMESTAMPDIFF(SECOND, createdAt, updatedAt) ASC;

-- 按毫秒级差值升序排序
SELECT *
FROM your_table
ORDER BY (UNIX_TIMESTAMP(updatedAt) * 1000 - UNIX_TIMESTAMP(createdAt) * 1000) ASC;

PostgreSQL #

可提取时间间隔的秒数，或直接用日期差的间隔类型排序：

-- 提取秒数后升序排序
SELECT *
FROM your_table
ORDER BY EXTRACT(EPOCH FROM (updatedAt - createdAt)) ASC;

-- 直接用间隔值升序排序
SELECT *
FROM your_table
ORDER BY (updatedAt - createdAt) ASC;

内容的提问来源于stack exchange，提问作者Ghanshyam Baviskar