Date Field not properly recognised by Kibana 4

[sharon.c]

I have two date fields in index, record_time and @timestamp, the values of these two fields should be the same, and their format is different. record_time is generated by php code, and @timestamp is generated by logstash automatically.

This is the screen catch of Kibana indice setting, you can see they are both recognised as date field

This is the screen catch of the two field presentation in Kibana discover page

This is the screen catch of the raw data format of the two field presentation in Kibana discover page

The following is screen catch when I use record_time field range aggregation on Kibana to aggregate count for each hour

The following is screen catch when I use @timestamp field range aggregation on Kibana to aggregate count for each hour

You can see when I use @timestamp , I can get aggregated result, but when I use record_time the aggregated result is not 0 ( kibana does not recognise record_time field properly)

Not only in date range visualisation, but also other visualisation like date histogram, if I use record_time the result is empty.

How can I make Kibana use this field correctly?

[LeeDr (Lee Drengenberg)]

Hi Sharon,

See if this resolves your issue; Date Histogram Problem

Regards,
Lee

[sharon.c]

Hi LeeDr, Thank you for your prompt reply.
I actually tried, it still does not work.
when I use @timestamp it works fine, I can see the data from discovery page,
but when I choose record_time when I add new index in kibana

It does not show any documents in the discover page.

Actually Elasticsearch does not respond properly if I use record_time to do time range query in Sense interface, but if I use @timetamp to define the range, the result is correct:

[LeeDr (Lee Drengenberg)]

Hi Sharon,
What version of Kibana are you on? I might start up that version and try to replicate your case since I'm running a 5.x version.

I'm not sure yet what the problem is but I can show you what an index of mine with 2 date type fields looks like as a comparison.

In this index I have a @timestamp and system.process.cpu.start_time
The date formats of both of mine are the same with the T between the date and time parts.
And in the JSON view, after the _source block, there is a "fields" block which shows those 2 timestamps with their milliseconds-since-the-epoch number format. Do you have that "fields" section? It might not be the same since I'm on 5.x and you're on Kibana 4.x.

And I confirmed I can create another index pattern using my system.process.cpu.start_time as the time-field name, and my Discover tab works fine.

And I also opened this to see the mapping; http://localhost:9200/metricbeat-2017.01.18/_mapping and I see those 2 date fields again.

[sharon.c]

I am using Kibana 4.5 with Elasticsearch 2.3
Maybe my date field is not in correct format, that's why Elasticsearch does not use it properly.
The date format I am using is in this format yyyy-mm-dd HH:mm:ss, when I use logstash to import the values and I defined this date field in elasticsearch template, there is no error from logstash during the importing process.

[Clinton_Gormley]

@sharon.c The one difference between those two fields is that the @timestamp includes time zone, so is converted to UTC before being indexed, while record_time doesn't and so is treated as UTC already.

In the screen capture of the raw data format, you can see that the two values aren't the same. @timestamp has time 19:49 while record_time has time 14:49. In the screen cap of the field presentation just above that, the two dates are both rendered as 14:49, I think because Kibana sees that @timestamp has a time zone and so converts it to your local time zone.

I'm guessing at the reason here as I don't know how much data you have or what periods it covers. The raw JSON query that you show is a bit weird, as it is looking for data between Aug 1 2016 to Sep 7 2016.

Try doing the following:

GET elkstats_record_leads*/_validate_query?explain
{
  "query": {
    "range": {
      "@timestamp": {
        "gte": ".....some value...",
        "lte": ".....some value..."
      }
    }
  }
}

And then substitute record_time for @timestamp and compare the outputs