# Timelion visualisation for an index with separate time and date columns to represent time series data

**URL:** <https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997>\
**Category:** Kibana\
**Tags:** timelion\
**Created:** [December 16, 2019, 11:40am UTC](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997 "2019-12-16T11:40:04Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![reddihareesh](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@reddihareesh](https://discuss.elastic.co/u/reddihareesh)\
**Post date:** [December 16, 2019, 11:40am UTC](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997/1 "2019-12-16T11:40:04Z")

</div>

i have a time series data of past 15 years in a cassandra table. The data points are separated by 1 minute equal intervals (hence each day have 1440 data points, and the first data point on each day starts at "yyyy-mm-dd 00:00:00" and the last point is "yyyy-mm-dd 23:59:00"), therefore the timestamp is divided into two separate columns as **"date"** (partition key) and **"time"** (primary key). And the table is then pipe lined to elasticsearch.  
But while creating kibana **index pattern** for the data, in **Time Filter field name** drop down, I could able to select only the **"date"** , but not along with **"time"** field, so I selected only **"date"** field and created index pattern.  
Now can this be treated as time series data set in kibana.  
How to use both **"date"** and **"time"** as we use **@timestamp** to plot on **timelion**

---

<div class="post-metadata">

**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\
**Post date:** [December 16, 2019, 11:33pm UTC](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997/2 "2019-12-16T23:33:41Z")

</div>

Hey @reddihareesh, Kibana doesn't support having separate "date" and "time" fields. Can you reindex your data to combine the date and time into a single "date" field? The [date datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html) actually stored dates and times with millisecond accuracy.

---

<div class="post-metadata">

**Author:** ![reddihareesh](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@reddihareesh](https://discuss.elastic.co/u/reddihareesh)\
**Post date:** [December 17, 2019, 12:41pm UTC](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997/3 "2019-12-17T12:41:34Z")

</div>

Thank you. And where do you mean to re-index, at elasticsearch or table in my cassandra data base.  
(Actually in cassandra there are separate datatypes for both [date and time])([https://docs.datastax.com/en/dse/5.1/cql/cql/cql\_reference/refDataTypes.html#refDataTypes\_\_date](https://docs.datastax.com/en/dse/5.1/cql/cql/cql_reference/refDataTypes.html#refDataTypes__date))  
I have created my cassandra table with the following create-query, but when check the mappings in elasticsearch i found that datatype of the time field is changed to text (and some other fields got attached) as you can see. So is re-indexing is the only solution?

```
        CREATE TABLE srldc.test_dupes_2year (
            date date,
            time time,
            alpur_ap_line_mw double,
            alpur_ka_line_mw double,
            PRIMARY KEY (date, time)
        )

[http://localhost:9200/test_du*/_mapping](http://)
    {
    	"test_dupes_2year": {
    		"mappings": {
    			"properties": {
    				"@timestamp": {
    					"type": "date"
    				},
    				"@version": {
    					"type": "text",
    					"fields": {
    						"keyword": {
    							"type": "keyword",
    							"ignore_above": 256
    						}
    					}
    				},
    				"alpur_ap_line_mw": {
    					"type": "float"
    				},
    				"alpur_ka_line_mw": {
    					"type": "float"
    				},
    				"date": {
    					"type": "date"
    				},
    				"time": {
    					"type": "text",
    					"fields": {
    						"keyword": {
    							"type": "keyword",
    							"ignore_above": 256
    						}
    					}
    				}
    			}
    		}
    	}
    }
```

---

<div class="post-metadata">

**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\
**Post date:** [December 17, 2019, 2:30pm UTC](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997/4 "2019-12-17T14:30:23Z")

</div>

Hey @reddihareesh, Kibana only reads data from Elasticsearch, so you'll want to switch to a date and time field in Elasticserach. You don't have to change your Cassandra configuration.

---

<div class="post-metadata">

**Author:** ![reddihareesh](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@reddihareesh](https://discuss.elastic.co/u/reddihareesh)\
**Post date:** [December 26, 2019, 5:36am UTC](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997/5 "2019-12-26T05:36:40Z")

</div>

Thank you @[Brandon\_Kobel](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997/4). I have reindexed my data by combining the date and time fields to form a field called datetime whose details are..

```
  "datetime" : {
    "type" : "date",
    "format" : "date_hour_minute_second"
  } 

GET my_reindex_data
{
  "my_reindex_data" : {
    "aliases" : { },
    "mappings" : {
      "properties" : {
	"date" : {
          "type" : "date"
        },
        "datetime" : {
          "type" : "date",
          "format" : "date_hour_minute_second"
        },
        "time_stamp" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }
}

GET my_reindex_data/_search
    "time_stamp" : "51960000000000",
    "date" : "2016-02-03",
    "datetime" : "2016-02-03T14:26:00"

```

But on Discovery (in all), when I click on (+) or (-) on top 5 values of `datetime` field, it gives following error (but if I write filters in this way `datetime>= "2016-01-22T10:00:00" and datetime <= "2016-01-22T10:29:00"` it works) . Am I doing anything wrong?

```
Error loading data
Request to Elasticsearch failed: {"error":{"root_cause":[{"type":"parse_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]: [failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]]"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"srldc_test_2years_data","node":"dyeCfRm-TMi9a7IUx9fbKQ","reason":{"type":"query_shard_exception","reason":"failed to create query: {\n \"bool\" : {\n \"must\" : [\n {\n \"match_phrase\" : {\n \"datetime\" : {\n \"query\" : \"2016-01-22T10:29:00.000Z\",\n \"slop\" : 0,\n \"zero_terms_query\" : \"NONE\",\n \"boost\" : 1.0\n }\n }\n },\n {\n \"range\" : {\n \"datetime\" : {\n \"from\" : null,\n \"to\" : null,\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n],\n \"filter\" : [\n {\n \"bool\" : {\n \"filter\" : [\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"datetime\" : {\n \"from\" : \"2016-01-22T10:00:00\",\n \"to\" : null,\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"time_zone\" : \"UTC\",\n \"boost\" : 1.0\n }\n }\n }\n],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n },\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"datetime\" : {\n \"from\" : null,\n \"to\" : \"2016-01-22T10:29:00\",\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"time_zone\" : \"UTC\",\n \"boost\" : 1.0\n }\n }\n }\n],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"boost\" : 1.0\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"boost\" : 1.0\n }\n}","index_uuid":"kCVD5sELRVKXcWbhYPGEQw","index":"srldc_test_2years_data","caused_by":{"type":"parse_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]: [failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]]","caused_by":{"type":"illegal_argument_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]","caused_by":{"type":"date_time_parse_exception","reason":"Text '2016-01-22T10:29:00.000Z' could not be parsed, unparsed text found at index 19"}}}}}],"caused_by":{"type":"parse_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]: [failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]]","caused_by":{"type":"illegal_argument_exception","reason":"failed to parse date field [2016-01-22T10:29:00.000Z] with format [date_hour_minute_second]","caused_by":{"type":"date_time_parse_exception","reason":"Text '2016-01-22T10:29:00.000Z' could not be parsed, unparsed text found at index 19"}}}},"status":400}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 23, 2020, 5:36am UTC](https://discuss.elastic.co/t/timelion-visualisation-for-an-index-with-separate-time-and-date-columns-to-represent-time-series-data/211997/6 "2020-01-23T05:36:41Z")

</div>

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