Sql select fails on date fields format epoch_second

Plugins SQL
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):
AWS OpenSearch Service 2.3

Describe the issue:
select a date field with format epoch_second fails with the error:

{
  "error": {
    "type": "IllegalStateException",
    "reason": "There was internal problem at backend",
    "details": "Construct ExprTimestampValue from \"1668106702\" failed, unsupported date format."
  },
  "status": 503
}

Fails with API and in Query Workbench in both SQL and PPL. Ingested data is an AWS VPC flow log. start and end fields are the data fields and contain integer values for epoch in seconds. Fields parse and display properly in Discover.

I can transform these fields to timestamp strings as part of etl, but really would like to understand why this fails.

Example query and results below.

POST _plugins/_sql
{
  "query": "select start from vpc-flow-logs-mapped limit 5;"
}

Return:

{
  "error": {
    "type": "IllegalStateException",
    "reason": "There was internal problem at backend",
    "details": "Construct ExprTimestampValue from \"1668106702\" failed, unsupported date format."
  },
  "status": 503
}

Configuration:

Here is the index mapping:

{
  "vpc-flow-logs-mapped" : {
    "mappings" : {
      "properties" : {
        "account-id" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "action" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "bytes" : {
          "type" : "integer"
        },
        "dstaddr" : {
          "type" : "ip"
        },
        "dstport" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "end" : {
          "type" : "date",
          "format" : "epoch_second"
        },
        "interface-id" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "log-status" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "packets" : {
          "type" : "integer"
        },
        "protocol" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "srcaddr" : {
          "type" : "ip"
        },
        "srcport" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "start" : {
          "type" : "date",
          "format" : "epoch_second"
        },
        "version" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }
}

Relevant Logs or Screenshots:

image
image1915Ɨ807 153 KB

2

Hi,
Thank you for sharing your case. The issue was already reported in a bug on GitHub: #794.
Please, track this bug for a fix. Iā€™m working on it right now, and I hope it will be published in the next release.
Currently supported formats are: strict_date_optional_time and epoch_millis. As a temporary workaround you can convert your data to match epoch_millis format.

1 Like