# SQL INTERVAL question

**URL:** <https://discuss.elastic.co/t/sql-interval-question/203485>\
**Category:** Elasticsearch\
**Created:** [October 14, 2019, 3:52pm UTC](https://discuss.elastic.co/t/sql-interval-question/203485 "2019-10-14T15:52:51Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rdesanno](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rdesanno/32/13607_2.png) [@rdesanno](https://discuss.elastic.co/u/rdesanno)\
**Post date:** [October 14, 2019, 3:52pm UTC](https://discuss.elastic.co/t/sql-interval-question/203485/1 "2019-10-14T15:52:51Z")

</div>

I'm trying to write a simple Elastic SQL that polls the temperature value in the last 3 minutes but i'm not able to select such a small timeframe. It should be as simple as NOW() - INTERVAL 3 MINUTES but that returns nothing.

If I increase the INTERVAL to 250 minutes, that somehow equates to the last 10 minutes and I don't understand how that can be.

What could I be doing wrong here?

```
sql> SELECT timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test*\" WHERE agent.hostname = 'b-1-1' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES ORDER BY TIME DESC LIMIT 1;
          TIME | TEMP
------------------------+---------------
2019-10-14T11:42:00.000Z|-166.9

sql> SELECT timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test*\" WHERE agent.hostname = 'b-1-1' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES ORDER BY TIME ASC LIMIT 1;
          TIME | TEMP
------------------------+---------------
2019-10-14T11:33:00.000Z|-166.9

```

241 seems like it represents the last minute but again, I am at a loss

```
sql> SELECT timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test*\" WHERE agent.hostname = 'b-1-1' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 240 MINUTES ORDER BY TIME ASC;
     TIME | TEMP
---------------+---------------

sql> SELECT timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test*\" WHERE agent.hostname = 'b-1-1' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 241 MINUTES ORDER BY TIME ASC;
          TIME | TEMP
------------------------+---------------
2019-10-14T11:50:00.000Z|-166.9
2019-10-14T11:50:00.000Z|-166.9
2019-10-14T11:50:00.000Z|-166.9

sql>

```

Any ideas?

---

<div class="post-metadata">

**Author:** ![William\_Brafford](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/william_brafford/32/51559_2.png) [@William\_Brafford](https://discuss.elastic.co/u/William_Brafford)\
**Post date:** [October 14, 2019, 5:26pm UTC](https://discuss.elastic.co/t/sql-interval-question/203485/2 "2019-10-14T17:26:15Z")

</div>

Hi, Rob.

I'm not able to replicate this on Elasticsearch 7.4. I used the following steps in the Kibana dev console.

1. Post mappings to get the right datatypes:

```auto
PUT sql_testing2
{
  "mappings": {
    "properties" : {
        "TEMPERATURE_A" : {
          "type" : "float"
        },
        "timestamp" : {
          "type" : "date"
        }
      }
  }
}

```

1. Post a document for testing (it's 17:19 UTC time, as I test):

```auto
PUT sql_testing2/_doc/2
{
  "timestamp": "2019-10-14T17:18:00.000Z",
  "TEMPERATURE_A": 312.2
}

```

1. Run a slightly modified version of your query against the REST API:

```auto
POST /_sql?format=txt
{
  "query": "SELECT timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM sql_testing2 WHERE TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 400 MINUTES"
}

          TIME | TEMP      
------------------------+---------------
2019-10-14T17:18:00.000Z|312.2     

```

Have you checked your logic around timezones? You can see what `now()` is returning by running `SELECT now() AS RIGHT_NOW;`.

I hope this is helpful!

-William

---

<div class="post-metadata">

**Author:** ![rdesanno](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rdesanno/32/13607_2.png) [@rdesanno](https://discuss.elastic.co/u/rdesanno)\
**Post date:** [October 14, 2019, 5:58pm UTC](https://discuss.elastic.co/t/sql-interval-question/203485/3 "2019-10-14T17:58:03Z")

</div>

You nailed it. It's the difference in timezones thats causing the offset and never occurred to me (it's 1:55 right now). Thanks for the assist!!

```
{
  "columns" : [
    {
      "name" : "RIGHT_NOW",
      "type" : "datetime"
    }
  ],
  "rows" : [
    [
      "2019-10-14T17:55:49.542Z"
    ]
  ]
}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 11, 2019, 6:02pm UTC](https://discuss.elastic.co/t/sql-interval-question/203485/4 "2019-11-11T18:02:06Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.