Kibana 5.0.1 used for range filter always EPOCH time

Elastic Stack Kibana
1

Browser: Chrome (latest) on Mac OSZ
Kibana 5.0.1 (Linux Ubuntu)

Hi,
When I want to build visualization at Kibana it generates request with range filter in UNIX format:

{
          "range": {
            "Regions.DateOfImport": {
              "gte": 1325777620838,
              "lte": 1483630420838,
              "format": "epoch_millis"
            }
 }

I changed Advanced settings for dateFormat as YYYY-MM-DD[T]HH:mm:ss.SSS[Z]

But it generates as UNIX epoch_millis which is elastic did not recognize and respond always with zero result.

In elastic datetime field value as YYYY-MM-DD[T]HH:mm:ss.SSS[Z]

Is there a bug or feature?

2

The dateFormat Advanced Setting is just for formatting dates as they are displayed to the user. That doesn't affect how Kibana forms its queries.

The problem could be due to the mapping of the Regions.DateOfImport field mapped as something other than date. Check the JSON output of the mapping API at: http://localhost:9200/my_index/my_type/_mapping, and see if it is a date field.

3

Well, it's date type - please see below, I've attached full index:

{
  "affiliate-mapped-data-20170105": {
    "mappings": {
      "external": {
        "dynamic_templates": [
          {
            "strings": {
              "match_mapping_type": "string",
              "mapping": {
                "index": "not_analyzed",
                "type": "string"
              }
            }
          }
        ],
        "properties": {
          "BrandName": {
            "type": "keyword",
            "copy_to": [
              "_search"
            ]
          },
          "ColorHexCode": {
            "type": "keyword"
          },
          "ColorName": {
            "type": "keyword",
            "copy_to": [
              "_search"
            ]
          },
          "Description": {
            "type": "keyword",
            "index": false,
            "copy_to": [
              "_search"
            ]
          },
          "GarmentType": {
            "type": "keyword"
          },
          "GarmentTypeId": {
            "type": "keyword"
          },
          "Gender": {
            "type": "keyword"
          },
          "IsAccessoryItem": {
            "type": "long"
          },
          "Keywords": {
            "type": "keyword",
            "copy_to": [
              "_search"
            ]
          },
          "ProductName": {
            "type": "keyword",
            "copy_to": [
              "_search"
            ]
          },
          "ProductType": {
            "type": "keyword"
          },
          "Regions": {
            "type": "nested",
            "properties": {
              "AffiliateColor": {
                "type": "keyword"
              },
              "AffiliateNetwork": {
                "type": "keyword"
              },
              "Country": {
                "type": "keyword"
              },
              "Currency": {
                "type": "keyword"
              },
              "DateOfImport": {
                "type": "date",
                "format": "date_optional_time||epoch_millis"
              },
              "Description": {
                "type": "keyword"
              },
              "IsOnSale": {
                "type": "boolean"
              },
              "Price": {
                "type": "float"
              },
              "ProductImageUrl": {
                "type": "keyword"
              },
              "ProductPageUrl": {
                "type": "keyword"
              },
              "Shop": {
                "type": "keyword"
              },
              "Size": {
                "type": "keyword",
                "index": false
              }
            }
          },
          "Size": {
            "type": "keyword",
            "index": false,
            "copy_to": [
              "_size"
            ]
          },
          "_search": {
            "type": "text",
            "store": true
          },
          "_size": {
            "type": "text",
            "store": true
          }
        }
      }
    }
  }
}
4

The mapping is epoch_millis and Kibana is generating query range parameters in epoch_millis so everything seems fine there.

In elastic datetime field value as YYYY-MM-DD[T]HH:mm:ss.SSS[Z]

Whatever format was used when in the JSON that you used to index the data, as long as Elasticsearch understands those values as dates, then using date ranges in milliseconds format in the query is totally fine.

Here's an example of how Elasticsearch can handle dates given with different formats and match it in queries where the range parameters are epoch_millis (I ran all this in Dev Tools Console):

Create a type:

PUT test
{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0
  }
}

PUT test/type/_mapping
{
  "properties": {
    "date_of_test": {
      "type": "date",
      "format": "date_optional_time||epoch_millis"
    },
    "message": {
      "type": "text"
    }
  }
}

Add some documents using different date formats:

POST test/type/1
{
  "date_of_test": "1483722369341",
  "message": "Test the first, using epoch_millis format"
}

POST test/type/2
{
  "date_of_test": "2017-01-06T17:07:51.041Z",
  "message": "Test the second, using ISO format"
}

POST test/type/3
{
  "message": "Test the third, no date here"
}

Make a query using a date range aggregation:

POST test/type/_search
{
  "size": 0,
  "aggs": {
    "results": {
      "date_range": {
        "field": "date_of_test",
        "ranges": [
          {
            "from": "1325777620838",
            "to": "2018-01-01T00:00:00.000Z"
          }
        ]
      }
    }
  }
}

Elasticsearch responds with the correct doc_count in the aggregation result:

  "aggregations": {
    "results": {
      "buckets": [
        {
          "key": "2012-01-05T15:33:40.838Z-2018-01-01T00:00:00.000Z",
          "from": 1325777620838,
          "from_as_string": "2012-01-05T15:33:40.838Z",
          "to": 1514764800000,
          "to_as_string": "2018-01-01T00:00:00.000Z",
          "doc_count": 2
        }
      ]
    }
  }

So as I showed, there's no problem with Kibana generating time as epoch_millis for the query.

Can you check in a conversion tool such as https://currentmillis.com/ to make sure you actually have data with DateOfImport values in the range of 1325777620838 - 1483630420838?

If your data checks out okay, can you provide more information about what type of visualization you are trying to create, and what your metrics and buckets look like, and how you are constructing the date range aggregation?

5

Thanks for great answer!

Yes it was issue in date time itself.

As I am figure out - elastic understand and make all conversions itsef

6

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