Elasticsearch 查询/聚合 date类型字段求差示例

需求背景

文档中有两个字段 start_time(采集时间), create_time(存储ES时间), 需要统计下从采集到入库的延迟

mapping

"mappings" : {
      "properties" : {
        
        "create_time" : {
          "type" : "date"
        },
        
        "start_time" : {
          "type" : "date"
        }
      }
    }
  }

查询

POST db_monitor/_search
{
  "size": 10,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "start_time": {
              "gte": "2021-11-08 00:00:00",
              "format": "yyyy-MM-dd HH:mm:ss",
              "time_zone": "+08:00"
            }
          }
        },
        {
          "script": {
            "script": "(doc['create_time'].value.toInstant().toEpochMilli()-doc['start_time'].value.toInstant().toEpochMilli())/1000 > 300"
          }
        }
      ]
    }
  },
  "sort": [
    {
      "total_count": {
        "order": "desc"
      }
    }
  ]
}

聚合

OST db_monitor/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "create_time": {
              "gte": "2021-11-08 00:00:00",
              "format": "yyyy-MM-dd HH:mm:ss",
              "time_zone": "+08:00"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "agg_by_term": {
      "terms": {
        "script": "(doc['create_time'].value.toInstant().toEpochMilli()-doc['start_time'].value.toInstant().toEpochMilli())/1000",
        "size": 100
      }
    }
  }
}