# Conver String to date (or replace @timestamp) (SOLVED) **URL:** \ **Category:** Logstash\ **Created:** [October 26, 2015, 7:13am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957 "2015-10-26T07:13:56Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Jason\_Zheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_zheng/32/4041_2.png) [@Jason\_Zheng](https://discuss.elastic.co/u/Jason_Zheng)\ **Post date:** [October 26, 2015, 7:13am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/1 "2015-10-26T07:13:56Z")
Hi All, I would like to convert the "string" to "date" and replace @timestamp or "string" convert to type "date", but it seems does not work, am I missing something following is my logstash conf setting ``` filter { ruby { code => " event['stock_date'] = Date.parse(event['stock_date']); " } date { locale => "zh-TW" timezone => "Asia/Taipei" match => ["stock_date", "YYYY-MM-dd;HH:mm:ss.SSS"] target => "@timestamp" } } ``` Result ``` "@timestamp" => "2015-10-26T07:08:30.440Z", "stock_date" => #, ``` Jason ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [October 26, 2015, 7:27am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/2 "2015-10-26T07:27:59Z")
Use the date filter to parse a string and convert it to an ISO8601 timestamp (usually stored in the `@timestamp`). No Ruby filter is necessary for this. Note that what happens on the Elasticsearch side isn't really up to Logstash. Logstash sends JSON documents to ES, and scalars in JSON documents are strings, numbers, or booleans. To have a timestamp stored as a date in ES you need to change the mapping. This is done by default for the `@timestamp` field. ---
**Author:** ![Jason\_Zheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_zheng/32/4041_2.png) [@Jason\_Zheng](https://discuss.elastic.co/u/Jason_Zheng)\ **Post date:** [October 26, 2015, 9:01am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/3 "2015-10-26T09:01:15Z")
> [@Jason\_Zheng](#): > > following is my logstash conf setting Hi Magnus, Thanks for replying, I tried ``` date { locale => "zh-TW" timezone => "Asia/Taipei" match => ["stock_date", "YYYY-MM-dd HH:mm:ss.SSS", "ISO8601"] target => "@timestamp" } output { elasticsearch { host => "140.92.25.42" #codec => plain { charset => "Big5" } codec => "json" protocol => "http" index => "logstash-%{+YYYY.MM.dd}" } } ``` But the result is that the es create a index by each record, shortly, 1000 lines \> 1000 indices ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [October 26, 2015, 9:09am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/4 "2015-10-26T09:09:35Z")
Let's first establish whether the date filter does its job. Is the timestamp in `stock_date` correctly converted to the `@timestamp` field? Pick some random documents and see what things looks like. Were those documents placed in the right index? For a document that ended up in the wrong index, what did `stock_date` and `@timestamp` look like? ---
**Author:** ![Jason\_Zheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_zheng/32/4041_2.png) [@Jason\_Zheng](https://discuss.elastic.co/u/Jason_Zheng)\ **Post date:** [October 26, 2015, 9:21am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/5 "2015-10-26T09:21:36Z")
1. Pick some logstash result 2. After a while Kibana (index pattern: logstash-\*) ![](https://us1.discourse-cdn.com/elastic/original/2X/f/fca747622644f1f50050ad805e350e036574d202.png) ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [October 26, 2015, 9:51am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/6 "2015-10-26T09:51:01Z")
It looks like the date filter is doing the right thing, but maybe you haven't considered the consequences of parsing `stock_date` into `@timestamp` and using time-series indexes. If `stock_date` is spread out over long periods of time (which the 2012-12-13 date in your example indicates) you _will_ get lots of indexes unless you reconfigure the elasticsearch output to not create one index per day. Possible solutions to your problem include: - Don't parse `stock_date' into`@timestamp`. - Scale up your cluster to deal with the thousands of indexes that are bound to occur. - Don't use time-series indexes at all, or scale them down to one index per year or one index per month instead of one index per day. What the best solution is depends on the specifics of what you want to accomplish, like e.g. how many documents you expect there to be per day (i.e. the number of unique `stock_date` values). ---
**Author:** ![Jason\_Zheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_zheng/32/4041_2.png) [@Jason\_Zheng](https://discuss.elastic.co/u/Jason_Zheng)\ **Post date:** [October 26, 2015, 10:04am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/7 "2015-10-26T10:04:35Z")
Hi Magnus if 'don't parse stock\_date into@timestamp', how to convert the field 'stock\_date' to 'date' type? I tried to modified output to one index As far as I can search the data in es curl -XGET '[http://140.92.25.42:9200/logstash-stock/\_search?pretty](http://140.92.25.42:9200/logstash-stock/_search?pretty)' ``` { "_index" : "logstash-stock", "_type" : "json", "_id" : "AVCjoAbeFCj-RUraIpaD", "_score" : 1.0, "_source":{"message":["2498\t宏達電\t20120322\t551.69\t560.51\t547.27\t549.04\t5750\t3607614\t-0.4800\t0.6748\t852052\t529976\t622.00\t623.00\t-0.4812\t2.413\t3.908\t4707\t8.05\t8.19\t5.84\t5.84\t\t1.15\t6.03\t5.71\t-3.00\t\t\t\tTSE\r"],"@version":"1","@timestamp":"20120321-12-31T16:00:00.000Z","host":"swagger4es","path":"/tmp/test12/HTC.txt","type":"json","stock_no":"2498","stock_name":"宏達電","stock_beggin_val":"551.69","stock_high_val":560.51,"stock_low_val":"547.27","stock_end_val":"549.04","stock_tarns":"5750","stock_trans_val":"3607614","stock_reward_rate":"-0.4800","stock_inv_rate":"0.6748","stock_out":"852052","stock_value":"529976","stock_last_buy":"622.00","stock_last_sale":"623.00","stock_reward_ln":"-0.4812","stock_mark_rate":"2.413","stock_trans_rate":"3.908","stock_trans_count":"4707","stock_eps_tse":"8.05","stock_eps_tej":"8.19","stock_price_tse":"5.84","stock_price_tej":"5.84","stock_up_down_stop":null,"stock_r_tej":"1.15","stock_bonus_rate_tse":"6.03","stock_cash_rate":"5.71","stock_up_down":"-3.00","stock_a":null,"stock_d":null,"stock_y":null,"stock_market":"TSE"} } ``` But it cannot be found in Kibana ![](https://us1.discourse-cdn.com/elastic/original/2X/4/4b7390c8f8bf0839636a75ede8e1c7774bf83caa.png) Jason ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [October 26, 2015, 11:42am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/8 "2015-10-26T11:42:23Z")
> if 'don't parse stock\_date into@timestamp', how to convert the field 'stock\_date' to 'date' type? You need to adjust the mappings of the index (probably through an index template). > But it cannot be found in Kibana That's because the timestamp in your example looks like this: ``` @timestamp":"20120321-12-31T16:00:00.000Z ``` This mean Dec 31 of the year 20120321. ---
**Author:** ![Jason\_Zheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_zheng/32/4041_2.png) [@Jason\_Zheng](https://discuss.elastic.co/u/Jason_Zheng)\ **Post date:** [October 27, 2015, 3:50am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/9 "2015-10-27T03:50:14Z")
I try to create a template: ``` curl -XPUT 'http://localhost:9200/_template/logstash-stock' -d '{ "template": "logstash-stock-*", "settings": { "number_of_shards": 1, "number_of_replicas": 0, "index.refresh_interval": "5s" }, "mappings": { "stocks": { "properties": { "stock_date": { "type": "date", "format": "yyyy-MM-dd" }, "stock_high_val": { "type": "float" }, "stock_low_val": { "type": "float" }, "stock_end_val": { "type": "float" } } } } }' ``` But after data into es by logstash, and create 'logstash-stocks-\*' index pattern for kibana, then kibana shows > Mapping conflict! 4 fields are defined as several types (string, integer, etc) across the indices that match this pattern. You may still be able to use these conflict fields in parts of Kibana, but they will be unavailable for functions that require Kibana to know their type. Correcting this issue will require reindexing your data. ``` stock_date conflict stock_high_val conflict stock_low_val conflict stock_end_val conflict ``` Am I missing something? ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [October 27, 2015, 6:32am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/10 "2015-10-27T06:32:40Z")
I don't know what to add to what Kibana already says. The listed fields are mapped in different ways in different indexes and Kibana has problems dealing with that. I suggest you reindex the old data. ---
**Author:** ![Jason\_Zheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_zheng/32/4041_2.png) [@Jason\_Zheng](https://discuss.elastic.co/u/Jason_Zheng)\ **Post date:** [October 27, 2015, 11:29am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/11 "2015-10-27T11:29:48Z")
Thanks for suggestion finally I follow to "adjust mappings of the index (probably through an index template)." as you said, it can be work ---
**Author:** ![gaurav1424](https://avatars.discourse-cdn.com/v4/letter/g/97f17d/32.png) [@gaurav1424](https://discuss.elastic.co/u/gaurav1424)\ **Post date:** [January 22, 2016, 11:41pm UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/12 "2016-01-22T23:41:45Z")
> [@magnusbaeck](#): > > Use the date filter to parse a string and convert it to an ISO8601 timestamp (usually stored in the @timestamp). No Ruby filter is necessary for this. I have Syslog timestamp from event parsed by logstash as : "syslog\_timestamp" =\> "Jan 22 15:20:36" How do I convert into timestamp / time format (instead of string) so that It can be searchable and I can apply time based query in Elasticsearch ? I do this: date { target =\> "syslog\_timestamp" match =\> ["syslog\_timestamp", "MMM d HH:mm:ss", "MMM dd HH:mm:ss"] } Still output is : { "syslog\_timestamp" =\> "Jan 22 15:20:36", "@timestamp" =\> "2016-01-22T23:20:36.000Z" } ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [January 23, 2016, 4:16pm UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/13 "2016-01-23T16:16:23Z")
Looks good, assuming your timezone is UTC-8; keep in mind that `@timestamp` is UTC. That's not a problem since Kibana by default adjusts the `@timestamp` to the browser's timezone (and similar tools can trivially do the same thing). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 5:14am UTC](https://discuss.elastic.co/t/conver-string-to-date-or-replace-timestamp-solved/32957/14 "2017-07-06T05:14:41Z")