Date Math in Elasticsearch SQL

Elastic Stack Elasticsearch
1

I'm on ES 6.7.0 with X-Pack. Based on the documentation I cannot figure out how to subtract two dates to get the time delta. My times are stored in epoch_millis and mapped as shown below.

I've tried a variety of casting and using INTERVAL. I haven't found a single successful strategy.

Mapping:

          "session_stime" : {
            "type" : "date",
            "format" : "epoch_millis"
          },

Without casting or math:

GET /_xpack/sql?format=txt
{
    "query": """
      SELECT 
      max(session_stime),
      min(session_stime)
      FROM "dataset_*"
      """
}

Response

max(session_stime)|min(session_stime)
------------------+------------------
1.551035491645E12 |1.541957117399E12

Without casting and with math:

GET /_xpack/sql?format=txt
{
    "query": """
      SELECT 
      max(session_stime) -
      min(session_stime)
      FROM "dataset_*"
      """
}

Response:

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 2:7: first argument of [max(session_stime) -\n      min(session_stime)] must be [numeric], found value [max(session_stime)] type [datetime]"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 2:7: first argument of [max(session_stime) -\n      min(session_stime)] must be [numeric], found value [max(session_stime)] type [datetime]"
  },
  "status": 400
}

With casting and math:

GET /_xpack/sql?format=txt
    {
        "query": """
          SELECT 
          CAST(max(session_stime) AS DOUBLE) -
          CAST(min(session_stime) AS DOUBLE)
          FROM "dataset_*"
          """
    }

Response:

{
  "error": {
    "root_cause": [
      {
        "type": "class_cast_exception",
        "reason": "java.lang.Double cannot be cast to java.time.ZonedDateTime"
      }
    ],
    "type": "class_cast_exception",
    "reason": "java.lang.Double cannot be cast to java.time.ZonedDateTime"
  },
  "status": 500
}
2

For what it's worth, I can cast the field when not using a min() or max() function. And do math on it.

GET /_xpack/sql?format=txt
{
    "query": """
      SELECT 
      CAST(session_stime AS DOUBLE) * 2 
      FROM "dataset_*"
      LIMIT 1
      """
}

response:

CAST(session_stime AS DOUBLE) * 2
---------------------------------
3.102036724698E12
3

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