Break down of Time points

[sLuvpreet33 (Luvpreet Singh)]

status_start_time              | status_end_time               | status | product_id
 2017-11-08 12:43:40.742361+00 | 2017-11-08 13:00:03.618945+00 |      1 | 264
 2017-11-08 13:00:03.6755+00   | 2017-11-08 13:00:03.675727+00 |      2 | 264

I have some data in this format which tells about the status of some product. It says that between start time and end time, the product had status = 1.

So, I want to store it in elastic.

Look at the first record, It starts at 12:43 and ends at 13:00 on the same day.

Is it possible in elastic(kibana) that I enter a time in between lets say 12:45 and it tells me that at time the status was 1?

And like this it can tell me the status of any minute in between(12:44,12:46,12:47,12:48, ...12:59)?

[Mark_Harwood]

See range field types. They can represent spans of time.
You'd need to index each span as a doc with the appropriate start/end and status

[1] https://www.elastic.co/guide/en/elasticsearch/reference/current/range.html

[sLuvpreet33 (Luvpreet Singh)]

Hey @Mark_Harwood , Thanks for your help.

I just got a question for you.

 curl -XPUT 'localhost:9200/reporttest2?pretty' -H 'Content-Type: application/json' -d'          
{
  "mappings": {                                            
    "my_type": {
      "properties": {
        "site_name": {
          "type": "string"         
        }, "status": { "type": "string"},
        "time_frame": {
          "type": "date_range", 
          "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss||epoch_millis"
        }
      }
    }
  } 
}
'

This is the mapping of the index.

Now, I want this query,

curl -XPOST 'localhost:9200/reporttest2/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query" : {
    "range" : {
      "time_frame" : { 
        "gte" : "2015-11-03 11:45:00",
        "lte" : "2015-11-05 12:00:00" 
      }
    }
  } 
}
'

But this gives me error,

{
  "error" : {
"root_cause" : [
  {
    "type" : "parse_exception",
    "reason" : "failed to parse date field [2015-11-03 11:45:00] with format [strict_date_optional_time||epoch_millis]"
  }
],
"type" : "search_phase_execution_exception",
"reason" : "all shards failed",
"phase" : "query",
"grouped" : true,
"failed_shards" : [
  {
    "shard" : 0,
    "index" : "reporttest2",
    "node" : "sMI61x8iRnmZO1MtB7yu-Q",
    "reason" : {
      "type" : "query_shard_exception",
      "reason" : "failed to create query: {\n  \"range\" : {\n    \"time_frame\" : {\n      \"from\" : \"2015-11-03 11:45:00\",\n      \"to\" : \"2015-11-05 12:00:00\",\n      \"include_lower\" : true,\n      \"include_upper\" : true,\n      \"relation\" : \"within\",\n      \"boost\" : 1.0\n    }\n  }\n}",
      "index_uuid" : "tX-aDMwsR3uxq78hiHDaug",
      "index" : "reporttest2",
      "caused_by" : {
        "type" : "parse_exception",
        "reason" : "failed to parse date field [2015-11-03 11:45:00] with format [strict_date_optional_time||epoch_millis]",
        "caused_by" : {
          "type" : "illegal_argument_exception",
          "reason" : "Unrecognized chars at the end of [2015-11-03 11:45:00]: [ 11:45:00]"
        }
      }
    }
  }
]
 },
 "status" : 400

}

However, this query works,

curl -XPOST 'localhost:9200/reporttest2/_search?pretty' -H 'Content-Type: application/json' -d'
 {
  "query" : {
    "range" : {
      "time_frame" : { 
        "gte" : "2015-11-03",         
        "lte" : "2015-11-05"                                
      }
    }
  } 
}
'

Cannot I include time in the query? (like this , '2015-11-02 12:00:00')

Please reply, thanks.