# Elasticsearch SQL - How to query data only from yesterday

**URL:** https://discuss.elastic.co/t/elasticsearch-sql-how-to-query-data-only-from-yesterday/299151
**Category:** Kibana
**Tags:** elastic-stack-sql
**Created:** [March 9, 2022, 12:39am UTC](https://discuss.elastic.co/t/elasticsearch-sql-how-to-query-data-only-from-yesterday/299151 "2022-03-09T00:39:20Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![ezra](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ezra/32/102785_2.png) [@ezra](https://discuss.elastic.co/u/ezra)
#### Post date: [March 9, 2022, 12:39am UTC](https://discuss.elastic.co/t/elasticsearch-sql-how-to-query-data-only-from-yesterday/299151/1 "2022-03-09T00:39:20Z")

</div>

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.

---

<div class="post-metadata">

### Author: ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)
#### Post date: [March 10, 2022, 3:38pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-how-to-query-data-only-from-yesterday/299151/2 "2022-03-10T15:38:44Z")

</div>

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.

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

```

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 7, 2022, 3:39pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-how-to-query-data-only-from-yesterday/299151/3 "2022-04-07T15:39:29Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.