Date_Diff function not working as expeceted

Elastic Stack Kibana
1

Query works well without this
"EVAL dd_ms = DATE_DIFF("day", last_data, latest_record)"

last_data | latest_record
2026-03-05T04:58:55.453Z|2026-03-04T17:09:30.155Z|

POST /_query?format=txt
{
  "query": """
  FROM .ml-anomalies*
|   WHERE missing_field_count > 50
|   STATS 
      last_data = MAX(last_data_time),
      latest_record = MAX(latest_record_timestamp),
      search_count = MAX(search_count),
      missing_count = MAX(missing_field_count),
      search_time = Max(total_search_time_ms)
    BY job_id
    EVAL dd_ms = DATE_DIFF("day", last_data, latest_record)
|   SORT missing_count DESC
|   LIMIT 100
    """
}
2

What, if any, error do you get? Does the query work in Kibana DevTools?

As posted, you are missing a pipe | character in the EVAL line.

This is your 19th new thread in less than a month. In an earlier thread, when I politely suggested perhaps invest some time in Elastic training to help build some knowledge, you told me you did not want to be spoon fed, I find the thread count a little bit ironic in that light. IMO you would still get more value, and perhaps faster progress, by spending some time understanding the fundamentals.

3

@RainTown

Lets set the record straight.

If there is time to spare in this work of mine. I would rather spend with my family and not touch elastic at all.

If its not because of current economic context and IT retrenchment and to bring income to the family to feed mouths i would not have choose this job.

Elastic so call vendor came and left a mess. So now with my limited elastic knowledge i am trying my best to clean it up.

If my memory serves me right there is no KPI in this forum so u can choose to ignore if u see my post .

Happy weekend . thanks very much

4 
last_data | latest_record
2026-03-05T04:58:55.453Z|2026-03-04T17:09:30.155Z|

@Whoami1980

You have to cast to date before

POST /_query?format=txt
{
  "query": """
  FROM .ml-anomalies*
|   WHERE missing_field_count > 50
|   STATS 
      last_data = MAX(last_data_time),
      latest_record = MAX(latest_record_timestamp),
      search_count = MAX(search_count),
      missing_count = MAX(missing_field_count),
      search_time = Max(total_search_time_ms)
    BY job_id
|   EVAL dd_ms = DATE_DIFF("day", TO_DATETIME(last_data), TO_DATETIME(latest_record)) <<< THIS 
|   SORT missing_count DESC
|   LIMIT 100
    """
}

Also I notice you have

| EVAL dd_ms = DATE_DIFF("day", TO_DATETIME(last_data), TO_DATETIME(latest_record))

But the var is dd_ms so perhaps this is more correct

| EVAL dd_ms = DATE_DIFF("ms", TO_DATETIME(last_data), TO_DATETIME(latest_record))

Protip to test when you get down to it you can do stuff like this

ROW last_data="2026-03-05T04:58:55.453Z", latest_record="2026-03-04T17:09:30.155Z"
| EVAL dd_ms = DATE_DIFF("ms", TO_DATETIME(last_data), TO_DATETIME(latest_record))

{
  "last_data": "2026-03-05T04:58:55.453Z",
  "latest_record": "2026-03-04T17:09:30.155Z",
  "dd_ms": -42565298
}
5

That works. Noted on your explanation will take note for future reference.

1 Like