Subdate/date_sub query method not supported

Plugins SQL
1

I must be doing something wrong, but I’m not sure what …

I’m trying to do a SQL query like this (names change to protect the guilty):

POST /_plugins/_sql
{
  "query": "SELECT nodename,state FROM nodes WHERE @timestamp > subdate(now(), INTERVAL 5 MINUTE)"
}

What I want is all the name/state pairs from the past 5 minutes from the index ‘nodes’. What I get is the error message:
"The following query method is not supported: subdate"

Looking at the documentation (and searching discourse and the github site), I see there are two functions that appear to be similar: ‘subdate’ and ‘date_sub’ … both give me the same error.

What am I doing wrong? Is there a better way to accomplish this goal?

I’m using the tarball installation of version 1.3.0 …

1 Like
2

Hi @DrEdWilliams - I’m still coming up to speed on some of this, but at first glance, the syntax of your query looks correct. I suspect there might be some kind of data type mismatch issue going on - is @timestamp configured as a date type in your index mapping? OpenSearch will do its best to detect the appropriate mapping if you haven’t explicitly provided one, but it’s not always perfect at it.

I’ll see if I can consult some subject matter experts for you.

Nate

3

@nateynate - @timestamp is the standard timestamp field coming in from metricbeat

4

Thanks for reporting the issue! I just did a quick test and found function NOW() seems broken. Both SUBDATE and INTERVAL works well as below, although SELECT NOW() gave me NPE. I’m investigating and looking into the code.

POST _plugins/_sql
{
  "query": """
    SELECT subdate(date('2008-01-02'), INTERVAL 5 MINUTE)
  """
}
{
  "schema": [
    {
      "name": """subdate(date('2008-01-02'), INTERVAL 5 MINUTE)""",
      "type": "datetime"
    }
  ],
  "datarows": [
    [
      "2008-01-01 23:55:00"
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}
1 Like
5

Whoah, I think that was some kind of speed record in bug identification. Thanks for bringing it here, @DrEdWilliams

Nate

6

@daichen - That makes sense!

Further experimentation with the adddate/subdate functions seem to work if I provide fields instead of the now() function – an dwhenever I try to pull in ‘now()’, I have issues.

It’s probably related, but the ‘curdate()’ function seems to cause the same behavior – I expect they are connected behind the scenes, but something else to check.

Thanks!

1 Like
7

@DrEdWilliams Thanks for more info. After looking into the code, I can confirm that some date functions like NOW() can only be used alone. Here is the issue opened earlier: Date time query improvement · Issue #46 · opensearch-project/sql · GitHub. I’ve commented there and we need to complete the date function support so they can work altogether. Sorry for the inconvenience caused!

8

Hello.
As a result, at the moment, how can it not be compared with the current date?
Faced the same problem.
[translate]

9

Here is the PR for adding the support: Add implementation of `now`, `sysdate`, `localtime` and similar functions by Yury-Fridlyand · Pull Request #754 · opensearch-project/sql · GitHub. Thanks!

1 Like
10

i still see the same issue when trying to query opensearch sql api for all records between now and 5 minutes ago.

ie:
POST /_plugins/_sql?format=csv
{
“query” : “SELECT count(*) FROM * WHERE @timestamp > subdate(now(), INTERVAL 5 MINUTE)”
}

Error:
“java.lang.RuntimeException: org.opensearch.sql.legacy.exception.SqlParseException: The following query method is not supported: subdate”

Am i missing something :)?

how else can i query opensearch for the last X minutes of data ?

1 Like