Elasticsearch SQL - How to query data only from yesterday

[ezra (Esraa Haouchar)]

Hello everyone.

I am looking to query data from yesterday, excluding the data from today. I have figured out how to do this when inputting a specific date and subtracting 1 day. However, I need it to be from today's date, for example timestamp = NOW() - 1d/d. This is on an element in Kibana Canvas, using Elasticsearch SQL.

This is what I have so far, however I would need to update the query everyday to change the specific date.

SELECT COUNT(XXX.keyword) AS Count

FROM "XXX"

WHERE XXX.keyword='XXX'

AND timestamp = '2022-03-09||-1d/d'

timestamp = NOW() - INTERVAL 2 DAYS would not work because it includes data from TODAY and YESTERDAY, whereas I only want the data from yesterday.

[jsanz (Jorge Sanz)]

I think this is what you need:

Getting the entries from metricbeat from yesterday using current day minus two and one day and truncated both to midnight.

GET _sql?
{
  "query": """
 SELECT COUNT(*)
   FROM "metricbeat-*"
  WHERE "@timestamp" 
BETWEEN DATETRUNC('day', (NOW() - INTERVAL 2 DAY)) 
    AND DATETRUNC('day', (NOW() - INTERVAL 1 DAY))
  """
}