# Elastic SQL query to get data of today ONLY

**URL:** <https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867>\
**Category:** Elasticsearch\
**Tags:** canvas\
**Created:** [June 4, 2020, 11:52pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867 "2020-06-04T23:52:07Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![jaffarkazi](https://avatars.discourse-cdn.com/v4/letter/j/977dab/32.png) [@jaffarkazi](https://discuss.elastic.co/u/jaffarkazi)\
**Post date:** [June 4, 2020, 11:52pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/1 "2020-06-04T23:52:07Z")

</div>

Through an Elastic SQL query, how can I retrieve data belonging to today only?

I've tried:

```
select "@timestamp", today(), now(),
TimeOfLastReceivedUpdate
FROM "index*"
where
Name = 'value'
and
("@timestamp" >= today() AND "@timestamp" <= NOW())

```

We're based in Australia.  
And today() and now() return  
2020-06-04T10:00:00+10:00 and 2020-06-05T09:48:09+10:00

now is correct, today is yesterdays date.

So what would I put in my where clause to get data of today only? TimeOfLastReceivedUpdate is our own field, @timestamp is the ES field.

Thanks  
Jaffar

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [June 7, 2020, 6:09pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/2 "2020-06-07T18:09:15Z")

</div>

- What version of ES are you on?
- How do you execute the query? JDBC? REST?
- Do you pass a timezone/time\_zone param?

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [June 7, 2020, 6:11pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/3 "2020-06-07T18:11:51Z")

</div>

If I execute:

```auto
POST /_sql?format=txt

{
    "query" : "select now(), today()",
    "time_zone": "Australia/Sydney"
}

```

I get:

```auto
            now() | today()           
-----------------------------+-----------------------------
2020-06-08T04:10:28.295+10:00|2020-06-08T00:00:00.000+10:00

```

which is correct, maybe some other transformation takes place in client side.

---

<div class="post-metadata">

**Author:** ![jaffarkazi](https://avatars.discourse-cdn.com/v4/letter/j/977dab/32.png) [@jaffarkazi](https://discuss.elastic.co/u/jaffarkazi)\
**Post date:** [June 10, 2020, 10:39pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/4 "2020-06-10T22:39:52Z")

</div>

How can I execute this as an ESQL query?

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [June 11, 2020, 4:28pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/5 "2020-06-11T16:28:06Z")

</div>

With curl, kibana console, postman, some es-client, etc.

---

<div class="post-metadata">

**Author:** ![jaffarkazi](https://avatars.discourse-cdn.com/v4/letter/j/977dab/32.png) [@jaffarkazi](https://discuss.elastic.co/u/jaffarkazi)\
**Post date:** [June 12, 2020, 12:40am UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/6 "2020-06-12T00:40:14Z")

</div>

Sorry for that.  
My question is - what would be the ESQL query for the same?

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [June 15, 2020, 12:08pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/7 "2020-06-15T12:08:08Z")

</div>

I don't get it sorry, what do you mean?

```auto
POST /_sql?format=txt

{
    "query" : "select \"@timestamp\", today(), now(), TimeOfLastReceivedUpdate FROM \"index*\" where Name = 'value' and (\"@timestamp\" >= today() AND \"@timestamp\" <= NOW())",
    "time_zone": "Australia/Sydney"
}

```

This ?

---

<div class="post-metadata">

**Author:** ![defalt](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/defalt/32/71379_2.png) [@defalt](https://discuss.elastic.co/u/defalt)\
**Post date:** [June 15, 2020, 12:20pm UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/8 "2020-06-15T12:20:02Z")

</div>

ES is capable of SQL since the newest version.

---

<div class="post-metadata">

**Author:** ![jaffarkazi](https://avatars.discourse-cdn.com/v4/letter/j/977dab/32.png) [@jaffarkazi](https://discuss.elastic.co/u/jaffarkazi)\
**Post date:** [June 18, 2020, 5:03am UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/9 "2020-06-18T05:03:03Z")

</div>

```auto
select "@timestamp", today(), now(),
TimeOfLastReceivedUpdate
FROM "index*"
where
Name = 'value'
and
("@timestamp" >= today() AND "@timestamp" <= NOW())

```

In the above query, just running the SQL, without the API as you have done, how can I specify the timezone is my question.

Thanks

---

<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:** [July 16, 2020, 5:03am UTC](https://discuss.elastic.co/t/elastic-sql-query-to-get-data-of-today-only/235867/10 "2020-07-16T05:03:04Z")

</div>

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