CODEWITHSUNDEEP
sqldatetimeelasticsearch
alphamikevictor
alphamikevictor

Reputation: 647

Querying ElasticSearch SQL filtering by @timestamp

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?

Upvotes: 2

Views: 4488

Answers (1)

alphamikevictor
alphamikevictor

Reputation: 647

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.

Upvotes: 4

Related Questions