Dates inserted as seconds since epoch aren't found by text date searchs

Elastic Stack Elasticsearch
1

Being new to Elasticsearch hopefully I'm missing something obvious.

I'm trying to insert data into ES with a timestamp. It is more convenient for the source data to use seconds since epoch but whenever I do this I can't seem to get results returned if I use a text date in the search query. I assume this is then why I am having problems visualising the data with Kibana.

I can illustrate the problem pretty simply using the following queries (the date used is 2017-04-14T17:10:59+0100 == 1492186259):

DELETE my_index
PUT my_index
{
  "mappings": {
    "my_type": {
      "properties": {
        "date": {
          "type":   "date",
          "format": "strict_date_optional_time||epoch_millis||epoch_second"
        }
      }
    }
  }
}

PUT my_index/my_type/1
{
    "date" : 1492186259
}

GET my_index/_search
{
    "query": {
        "range" : {
            "date" : {
                "gte" : "2017-04-14T12:00:00+01:00"
            }
        }
    }
}

This will not return any results but searching using a time formatted as seconds since epoch works:

GET my_index/_search
{
    "query": {
        "range" : {
            "date" : {
                "gte" : 1492167600
            }
        }
    }
}

If I insert the date in the text format, both searches work perfectly:

PUT my_index/my_type/1
{
    "date" : "2017-04-14T17:10:59+0100"
}

Even though I've explicitly set the date formats to use and the index is reporting that it is expecting a date it looks like if I use seconds since epoch to insert a date I can only use numeric queries. What have I missed, if anything?

Elasticsearch is running on a fully patched CentOS 7 install accessed through localhost with stock config other than hostname. ES version info:

{
  "name" : "juFPZaL",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "KwbzM__GSCS_x0AD0G8C1g",
  "version" : {
    "number" : "5.3.0",
    "build_hash" : "3adb13b",
    "build_date" : "2017-03-23T03:31:50.652Z",
    "build_snapshot" : false,
    "lucene_version" : "6.4.1"
  },
  "tagline" : "You Know, for Search"
}
2

My full terminal output using curl to run the queries

[pgellex@escentos7 ~]$ curl -XDELETE 'localhost:9200/my_index/'
{"acknowledged":true}
[paulg@paulgcentos7 ~]$ curl -XPUT 'localhost:9200/my_index?pretty' -H 'Content-Type: application/json' -d'
{             
  "mappings": {
    "my_type": {      
      "properties": {
        "date": {
          "type":   "date",
          "format": "strict_date_optional_time||epoch_millis||epoch_second"
        }
      }
    }
  }
}
'
{
  "acknowledged" : true,
  "shards_acknowledged" : true
}
[pgellex@escentos7 ~]$ curl -XGET 'localhost:9200/my_index?pretty'
{
  "my_index" : {
    "aliases" : { },
    "mappings" : {
      "my_type" : {
        "properties" : {
          "date" : {
            "type" : "date",
            "format" : "strict_date_optional_time||epoch_millis||epoch_second"
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "creation_date" : "1492418644648",
        "number_of_shards" : "5",
        "number_of_replicas" : "1",
        "uuid" : "PWtDsg0hSgGmopM90a80sg",
        "version" : {
          "created" : "5030099"
        },
        "provided_name" : "my_index"
      }
    }
  }
}
[pgellex@escentos7 ~]$ curl -XPUT 'localhost:9200/my_index/my_type/1?pretty' -H 'Content-Type: application/json' -d'
{             
    "date" : 1492186259
}                     
'
{
  "_index" : "my_index",
  "_type" : "my_type",
  "_id" : "1",
  "_version" : 1,
  "result" : "created",
  "_shards" : {
    "total" : 2,
    "successful" : 1,
    "failed" : 0
  },
  "created" : true
}
[pgellex@escentos7 ~]$ curl -XGET 'localhost:9200/my_index/_search?pretty' -d'{                                    
    "query": {
        "range" : {
            "date" : {
                "gte" : "2017-04-14T12:00:00+01:00"
            }
        }
    }
}
'
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 0,
    "max_score" : null,
    "hits" : [ ]
  }
}
[pgellex@escentos7 ~]$ curl -XGET 'localhost:9200/my_index/_search?pretty' -d'
{
    "query": {
        "range" : {
            "date" : {
                "gte" : 1492167600
            }
        }
    }
}
'
{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_index",
        "_type" : "my_type",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "date" : 1492186259
        }
      }
    ]
  }
}
[pgellex@escentos7 ~]$ curl -XPUT 'localhost:9200/my_index/my_type/1?pretty' -H 'Content-Type: application/json' -d'
{             
    "date" : "2017-04-14T17:10:59+0100"
}                     
'
{
  "_index" : "my_index",
  "_type" : "my_type",
  "_id" : "1",
  "_version" : 2,
  "result" : "updated",
  "_shards" : {
    "total" : 2,
    "successful" : 1,
    "failed" : 0
  },
  "created" : false
}
[pgellex@escentos7 ~]$ curl -XGET 'localhost:9200/my_index/_search?pretty' -d'{                                    
    "query": {
        "range" : {
            "date" : {
                "gte" : "2017-04-14T12:00:00+01:00"
            }
        }
    }
}
'
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_index",
        "_type" : "my_type",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "date" : "2017-04-14T17:10:59+0100"
        }
      }
    ]
  }
}
[pgellex@escentos7 ~]$ curl -XGET 'localhost:9200/my_index/_search?pretty' -d'{                                    
    "query": {
        "range" : {
            "date" : {
                "gte" : 1492167600
            }
        }
    }
}
'
{
  "took" : 24,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_index",
        "_type" : "my_type",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "date" : "2017-04-14T17:10:59+0100"
        }
      }
    ]
  }
}
[pgellex@escentos7 ~]$
3

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