Elastic SQL query to get data of today ONLY

Elastic Stack Elasticsearch
1

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

2
  • What version of ES are you on?
  • How do you execute the query? JDBC? REST?
  • Do you pass a timezone/time_zone param?
3

If I execute:

POST /_sql?format=txt

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

I get:

            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.

4

How can I execute this as an ESQL query?

5

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

6

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

7

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

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 ?

8

ES is capable of SQL since the newest version.

9 
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

10

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