elasticsearch - 如何编写脚本字段以获取状态转换之间的天数

Question

我在名为created_on和closed_on的索引中有两个字段，其日期格式为2019年7月15日17:40:09.000，我想编写一个易于编写的脚本字段来计算这两个日期之间的天数。

我已经试过了:

def res_date= 0; 
def closing_date = 0;
if ( doc['status_id'].value == 51 ) {
res_date = doc['e_created_date'].date.getMillis();
}
if ( doc['status_id'].value == 20 ) {
closing_date = doc['closed_on'].date.getMillis();
}
def day = 0;
day= (closing_date - res_date) / (1000 * 60 * 60 * 24);  
return day;

我的日值错误。

Sample data

Answer 1

因此，您需要按issue_id进行汇总，然后找到问题打开的时间(状态20)到关闭问题的时间(状态51)之间的天数。

您应该能够运行以下查询，并在响应的diff_days存储桶字段中找到所需的确切信息:

GET test2/_search
{
  "size": 0,
  "aggs": {
    "issues": {
      "terms": {
        "field": "issue_id",
        "size": 10
      },
      "aggs": {
        "openedStatus": {
          "filter": {
            "term": {
              "status_id": "51"
            }
          },
          "aggs": {
            "openedDate": {
              "min": {
                "field": "created_on"
              }
            }
          }
        },
        "closedStatus": {
          "filter": {
            "term": {
              "status_id": "20"
            }
          },
          "aggs": {
            "closedDate": {
              "min": {
                "field": "closed_on"
              }
            }
          }
        },
        "diff_days": {
          "bucket_script": {
            "buckets_path": {
              "opened": "openedStatus>openedDate",
              "closed": "closedStatus>closedDate"
            },
            "script": "(params.closed - params.opened) / 86400000"
          }
        }
      }
    }
  }
}

结果=>

"issues" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : 167893,
      "doc_count" : 3,
      "closedStatus" : {
        "doc_count" : 1,
        "closedDate" : {
          "value" : 1.565005134E12,
          "value_as_string" : "2019-08-05T11:38:54.000Z"
        }
      },
      "openedStatus" : {
        "doc_count" : 2,
        "openedDate" : {
          "value" : 1.559556432E12,
          "value_as_string" : "2019-06-03T10:07:12.000Z"
        }
      },
      "diff_days" : {
        "value" : 63.06368055555556                     <--- 63 days
      }
    }
  ]
}