opendistro query for last n minutes

Question

{
    "query": "SELECT COUNT(*) AS result FROM my-index WHERE ['field1'] > 3"
}

above query gives result. I am trying to filter count on last 15 minutes. Tried below query

{
    "query": "SELECT COUNT(*) AS result FROM my-index WHERE ['field1'] > 3000 AND time >  NOW() - INTERVAL 15 MINUTE
}

which gives error

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Failed to parse SqlExpression of type class com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr. expression value: NOW() - INTERVAL 15 MINUTE",
    "type": "SqlParseException"
  },
  "status": 400
}

tried filtering with actual date time as well

time > 2022-06-04 14:00

but got error

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "illegal sql expr : <query>",
    "type": "ParserException"
  },
  "status": 400
}

Requesting to suggest how can I filter records of last n minutes?

I even tried range query

{
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "field1": {
                            "gte": 3
                        }
                    }
                },
                {
                    "range": {
                        "time": { "from" : "30 minutes ago", "to" : "now" }
                    }
                }
            ]
        }
    }
}

It does not give any result. If I remove time range query then it gives result

score 0 · Answer 1 · answered Jun 04 '22 at 13:07

I am able to filter using epoch timestamp

{
       "query": "SELECT COUNT(*) AS result FROM my-index WHERE ['field1'] > 3000 AND ['time'] > 1654338744000
}

gives result

{
    "took": 5,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 1958,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "result": {
            "value": 1958
        }
    }
}

opendistro query for last n minutes

1 Answers1