# Elasticsearch-sql-cli Syntax :: "For the Last Five Minutes..." Query? **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [October 22, 2019, 3:13pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-cli-syntax-for-the-last-five-minutes-query/204677 "2019-10-22T15:13:32Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![redapplesonly](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/redapplesonly/32/57700_2.png) [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)\ **Post date:** [October 22, 2019, 3:13pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-cli-syntax-for-the-last-five-minutes-query/204677/1 "2019-10-22T15:13:32Z")
Hi Elasticsearch Gurus, I’ve recently spun up an Elasticsearch v7.4.0 Docker container. Within the container, I’ve been playing with the “ **elasticsearch-sql-cli** ” script and successfully submitting SQL-like queries against my ES index. Which is pretty great. Here’s a follow-up problem. My index is really massive, with several million data records, and growing rapidly. I need to find a way to submit an SQL-like query that basically says, “Give me X data only _ **for the last five minutes** _.” As luck would have it, my index includes a “timestamp” record: ``` { "mapping": { "properties": { "@timestamp": { "type": "date" },… ``` Here’s a sample of what it looks like in a standard query: ``` Timestamp | SrcIP | DstIP |Protocol|Port| sum ------------------------+------------+------------+--------+----+------- 2019-10-09T20:02:27.770Z|10.10.10.15 |10.10.10.16 |6 |5201|15000.0 2019-10-09T20:02:27.771Z|10.10.10.15 |10.10.10.16 |6 |5201|51000.0 2019-10-09T20:02:27.772Z|10.10.10.15 |10.10.10.16 |6 |5201|19500.0 2019-10-09T20:02:27.785Z|10.10.10.15 |10.10.10.16 |6 |5201|69000.0 2019-10-09T20:02:27.787Z|10.10.10.15 |10.10.10.16 |6 |5201|10500.0 2019-10-09T20:02:27.788Z|10.10.10.15 |10.10.10.16 |6 |5201|10500.0 2019-10-09T20:02:27.789Z|10.10.10.15 |10.10.10.16 |6 |5201|24000.0 2019-10-09T20:02:27.790Z|10.10.10.15 |10.10.10.16 |6 |5201|54000.0 2019-10-09T20:02:27.791Z|10.10.10.15 |10.10.10.16 |6 |5201|55500.0 ``` Okay, so how would you say “_Give me records from the last five minutes?_” Standard SQL would have you do that [like this](https://stackoverflow.com/questions/23228133/how-can-query-last-5-minutes-of-records). But I’ve tried this in my **elasticsearch-sql-cli** script, and I’m missing something in the syntax. Here’s a few attempts: ``` sql> select \"@timestamp\", SrcIP, DstIP, Protocol, Port, sum(Total) > from \"myindex2019.10.09\" > where Port = 5201 AND > to_date(\"@timestamp\", 'MM/DD/YYYY HH24:MI:SS') >= sysdate - 5/(24*60) > group by \"@timestamp\", SrcIP, DstIP, Protocol, Port; > Bad request [Found 2 problem(s) line 4:7: Unknown function [to_date], did you mean any of [CURDATE, TODAY, LOCATE, TRUNCATE]? line 4:57: Unknown column [sysdate]] sql> sql> sql> select \"@timestamp\", SrcIP, DstIP, Protocol, Port, sum(Total) > from \"myindex2019.10.09\" > where Port = 5201 AND > \"@timestamp\" >= CURDATE - 5/(24*60) > group by \"@timestamp\", SrcIP, DstIP, Protocol, Port; > Bad request [Found 1 problem(s) line 4:23: Unknown column [CURDATE]] sql> sql> sql> select \"@timestamp\", SrcIP, DstIP, Protocol, Port, sum(Total) > from \"myindex2019.10.09\" > where Port = 5201 AND > \"@timestamp\" >= CURDATE() - 5/(24*60) > group by \"@timestamp\", SrcIP, DstIP, Protocol, Port; > Bad request [Found 1 problem(s) line 4:23: first argument of [CURDATE() - 5/(24*60)] must be [numeric], found value [CURDATE()] type [date]] sql> sql> ``` Any advice on how to crack this syntax? And… as a general question… Is there an online guide to the ES/SQL syntax? It will be pretty tedious if I have to post on the forums every time I have a general question like this. Thanks! ---
**Author:** ![redapplesonly](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/redapplesonly/32/57700_2.png) [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)\ **Post date:** [October 22, 2019, 6:07pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-cli-syntax-for-the-last-five-minutes-query/204677/2 "2019-10-22T18:07:20Z")
Update... I've been researching SQL functions that allow you to manipulate time datatypes. It seems that many of the functions which normally would be helpful here aren't available. `DATEADD(),` for instance, isn't supported: ``` sql> select DATEADD( minute, 5, CURRENT_TIMESTAMP()); Bad request [Found 1 problem(s) line 1:17: Unknown column [minute]] sql> sql> select DATEADD( 1, 1, CURRENT_TIMESTAMP ); Bad request [Found 1 problem(s) line 1:8: Unknown function [DATEADD], did you mean [DATABASE]?] sql> ``` I know that the ES implementation of SQL queries is "like SQL," but not actually SQL. Are there any online docs which help illustrate what is and is not supported? Thanks! ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 23, 2019, 4:20am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cli-syntax-for-the-last-five-minutes-query/204677/3 "2019-10-23T04:20:27Z")
[DATEADD](https://github.com/elastic/elasticsearch/pull/47747) and [DATEDIFF](https://github.com/elastic/elasticsearch/pull/47920) were added recently to es-sql and will be available in 7.5.0. But, in your specific scenario you should be able to do ```auto SELECT \"@timestamp\", SrcIP, DstIP, Protocol, Port, SUM(Total) \"myindex2019.10.09\" WHERE Port = 5201 AND \"@timestamp\" >= NOW() - INTERVAL 5 MINUTES GROUP BY \"@timestamp\", SrcIP, DstIP, Protocol, Port ``` So, basically `NOW() - INTERVAL 5 MINUTES`. Though, I would suggest not grouping by `@timestamp` - it will be a rather intensive aggregation (creating buckets at millisecond level). Regarding the documentation, I'd say it's pretty good and comprehensive. The language and syntax in general is covered [here](https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-spec.html). While the bit you are interested in is in the [INTERVALS](https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-functions-datetime.html#sql-functions-datetime-interval) section. If you have a any suggestions for improvements, please don't hesitate to ask for it in our [github repo](https://github.com/elastic/elasticsearch/) (Search/SQL label). Thanks. ---
**Author:** ![redapplesonly](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/redapplesonly/32/57700_2.png) [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)\ **Post date:** [October 23, 2019, 4:32pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-cli-syntax-for-the-last-five-minutes-query/204677/5 "2019-10-23T16:32:51Z")
> [@Andrei\_Stefan](#): > > AND "@timestamp" \>= NOW() - INTERVAL 5 MINUTES YES! This did the trick. Thank you, this is incredibly helpful. My apologies if I was grumbling about the SQL syntax. I did read through it before upgrading to 7.4.0, but that was weeks ago. I should have rescanned it before submitting this post. ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 24, 2019, 6:56am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cli-syntax-for-the-last-five-minutes-query/204677/6 "2019-10-24T06:56:23Z")
No worries @redapplesonly. Please, don't hesitate to ask further if you encounter any other roadblocks. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 21, 2019, 6:56am UTC](https://discuss.elastic.co/t/elasticsearch-sql-cli-syntax-for-the-last-five-minutes-query/204677/7 "2019-11-21T06:56:23Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.