# Subdate/date\_sub query method not supported

**URL:** <https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252>\
**Category:** SQL\
**Tags:** troubleshoot\
**Created:** [April 14, 2022, 10:49pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252 "2022-04-14T22:49:44Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![DrEdWilliams](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/dredwilliams/32/2256_2.png) [@DrEdWilliams](https://forum.opensearch.org/u/DrEdWilliams)\
**Post date:** [April 14, 2022, 10:49pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/1 "2022-04-14T22:49:44Z")

</div>

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):

```auto
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 …

---

<div class="post-metadata">

**Author:** ![nateynate](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/nateynate/32/4913_2.png) [@nateynate](https://forum.opensearch.org/u/nateynate)\
**Post date:** [April 14, 2022, 11:29pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/2 "2022-04-14T23:29:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![DrEdWilliams](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/dredwilliams/32/2256_2.png) [@DrEdWilliams](https://forum.opensearch.org/u/DrEdWilliams)\
**Post date:** [April 14, 2022, 11:37pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/3 "2022-04-14T23:37:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [April 14, 2022, 11:45pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/4 "2022-04-14T23:45:42Z")

</div>

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.

```auto
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
}

```

---

<div class="post-metadata">

**Author:** ![nateynate](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/nateynate/32/4913_2.png) [@nateynate](https://forum.opensearch.org/u/nateynate)\
**Post date:** [April 14, 2022, 11:50pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/5 "2022-04-14T23:50:16Z")

</div>

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

Nate

---

<div class="post-metadata">

**Author:** ![DrEdWilliams](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/dredwilliams/32/2256_2.png) [@DrEdWilliams](https://forum.opensearch.org/u/DrEdWilliams)\
**Post date:** [April 14, 2022, 11:50pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/6 "2022-04-14T23:50:40Z")

</div>

@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!

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [April 15, 2022, 12:07am UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/7 "2022-04-15T00:07:01Z")

</div>

@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](https://github.com/opensearch-project/sql/issues/46). I’ve commented there and we need to complete the date function support so they can work altogether. Sorry for the inconvenience caused!

---

<div class="post-metadata">

**Author:** ![VictorOM](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/victorom/32/3665_2.png) [@VictorOM](https://forum.opensearch.org/u/VictorOM)\
**Post date:** [July 29, 2022, 5:20pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/8 "2022-07-29T17:20:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\
**Post date:** [September 8, 2022, 6:13pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/9 "2022-09-08T18:13:36Z")

</div>

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](https://github.com/opensearch-project/sql/pull/754). Thanks!

---

<div class="post-metadata">

**Author:** ![coredump17](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@coredump17](https://forum.opensearch.org/u/coredump17)\
**Post date:** [February 2, 2024, 4:08pm UTC](https://forum.opensearch.org/t/subdate-date-sub-query-method-not-supported/9252/10 "2024-02-02T16:08:14Z")

</div>

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 ?
