Querying ElasticSearch SQL filtering by @timestamp

Question

I want to perform a query using SQL syntax in elastic search using URL /_sql and use in the where clause a filter for dates. So far if in the body I use this query

{
    "query": "SELECT count(*) from logstash where Severity='ERROR'"
}

I get some results, then if I try to filter by @timestamp

{
    "query": "SELECT Time from logstash where Severity='ERROR'  and '@timestamp' > NOW() - INTERVAL 30 MINUTES"
}

And instead of getting 0 in the count or a shorter number than before I simply get nothing if I remove the quotes around @timestamp there it raises an error (not expecting the @ symbol), and if I use timestamp then suggest me to use @timestamp as timestamp is not a valid field.

How can I filter by @timestamp? Or how should I escape the @ symbol for the field?

alphamikevictor · Accepted Answer · 2020-05-20 13:25:58Z

4

Instead of surrounding with simple quotes I needed to surround with double quotes, so:

{
    "query": "SELECT count(*) from logstash where Severity='ERROR' and \"@timestamp\" > NOW() - INTERVAL 30 MINUTES"
}

worked.

answered May 20, 2020 at 13:25

alphamikevictor

6478 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying ElasticSearch SQL filtering by @timestamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related