SQL INTERVAL question

[rdesanno (Rob)]

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?

[William_Brafford]

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:

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

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

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

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

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

[rdesanno (Rob)]

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"
    ]
  ]
}