# Problem with the date-filter (timezone)

**URL:** <https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145>\
**Category:** Logstash\
**Created:** [May 9, 2017, 5:40pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145 "2017-05-09T17:40:31Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 9, 2017, 5:40pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/1 "2017-05-09T17:40:31Z")

</div>

Hi,  
i'm new to elk, so it may be a layer 8 problem, but i'm not able to fix it. So i hope here's somebody able to help me.  
So currently the date-filter in my logstash config is not doing what i expect. I import csv Files with some date-fields in it. These have no timezone, so i added the date-filter like this:

```
date {
                locale => "de"
                match => ["Start", "dd.MM.yyyy HH:mm:ss"]
                timezone => "Europe/Berlin"
        }

```

This is how the field looks like in the csv-file:  
"02.05.2017 07:46:49"

After running logstash, this is what i get in Elasticsearch:

```
"Start": [
      1493711209000
    ],

```

which is in "human language" "Tue, 02 May 2017 07:46:49 GMT". But after running through my date-filter, it shoult be "Tue, 02 May 2017 07:46:49 GMT+2:00", or am i wrong?

Help will be appreciated.

---

<div class="post-metadata">

**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:** [May 9, 2017, 6:24pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/2 "2017-05-09T18:24:49Z")

</div>

Unless configured otherwise with the `target` option, the date filter writes the resulting timestamp to the `@timestamp` field.

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 9, 2017, 6:30pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/3 "2017-05-09T18:30:14Z")

</div>

Yes that's what i found out 5minutes ago. But if i add  
`target => "Start"`

i don't get any data in Elasticsearch anymore.  
This is what i found in the elasticsearch output:

```
Caused by: java.lang.IllegalArgumentException: Invalid format: "2017-05-01T06:34:52.000Z" is malformed at "17-05-01T06:34:52.000Z"
```

---

<div class="post-metadata">

**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:** [May 9, 2017, 6:32pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/4 "2017-05-09T18:32:35Z")

</div>

That's probably because the `Start` field has been mapped a particular way, and suddenly the data you're sending doesn't match. Can you just delete the index and start over?

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 9, 2017, 6:35pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/5 "2017-05-09T18:35:38Z")

</div>

I did that, still the same error. Do i have to delete the index patterns as well?

---

<div class="post-metadata">

**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:** [May 9, 2017, 6:37pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/6 "2017-05-09T18:37:31Z")

</div>

Index patterns in Kibana? No, you can leave them. When you get this error, what do the mappings of the index look like? Specifically the `Start` field. Use ES's get mapping API.

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 9, 2017, 6:40pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/7 "2017-05-09T18:40:23Z")

</div>

Mapping of Start:

```
          "Start" : {
            "type" : "date",
            "format" : "dd.MM.YYYY HH:mm:ss"
          },

```

This is my logstash configuration, if it is helpful:

```
input {
        file {
                path => ".../*.csv"
                start_position => "beginning"
                sincedb_path => "/dev/null"
        }
}

filter {
        csv {
                separator => ";"
                columns => ["UID","Start","Ende","IP","Land","Client", "Verbindungsdauer"]
        }
        geoip {
                source => "IP"
                database => ".../GeoLite2-City.mmdb"
        }
        grok {
                match => ["path",".../...-%{GREEDYDATA:radiocountry}_%{GREEDYDATA:log_month}\.csv"]

        }
        fingerprint {
                source => ["message"]
                target => "fingerprint"
                key => "***"
                method => "SHA1"
                concatenate_sources => true
        }
        date {
                locale => "de"
                match => ["Start", "dd.MM.yyyy HH:mm:ss"]
                timezone => "Europe/Berlin"
                target => "Start"
        }
}

output {
        elasticsearch {
                hosts => "http://localhost:9200"
                index => "...-%{radiocountry}"
                document_id => "%{fingerprint}"
        }
        stdout {}
}

```

Thank you very much for helping.

---

<div class="post-metadata">

**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:** [May 10, 2017, 5:43am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/8 "2017-05-10T05:43:03Z")

</div>

Okay, so something resulted in that mapping of the `Start` field. Logstash doesn't do this out of the box and if your date filter works that's not what the `Start` field of your events will look like. Check again that you really have deleted the index so you're starting fresh and that the date filter always works.

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 10, 2017, 7:19am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/9 "2017-05-10T07:19:02Z")

</div>

I deleted all indizes and checked if the mapping was gone. After starting logstash, i get the same error as before.

---

<div class="post-metadata">

**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:** [May 10, 2017, 7:28am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/10 "2017-05-10T07:28:00Z")

</div>

What if you create the index by hand? What do the mappings look like for the newly created empty index? There shouldn't be any `Start` mapping. What if you insert a document with a `Start` field by hand?

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 10, 2017, 8:14am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/11 "2017-05-10T08:14:08Z")

</div>

Creating an empty index, just like "PUT testindex"?

---

<div class="post-metadata">

**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:** [May 10, 2017, 8:34am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/12 "2017-05-10T08:34:14Z")

</div>

Yes, exactly.

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 10, 2017, 8:42am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/13 "2017-05-10T08:42:01Z")

</div>

Ok, i never did this by hand so i hope this is right.

GET testindex:

```
{
  "testindex": {
    "aliases": {},
    "mappings": {},
    "settings": {
      "index": {
        "creation_date": "1494404299923",
        "number_of_shards": "5",
        "number_of_replicas": "1",
        "uuid": "ZWr-zuBARv6tmgPmNJHKgA",
        "version": {
          "created": "5020199"
        },
        "provided_name": "testindex"
      }
    }
  }
}

```

Putting a new document in it:

```
PUT textindex/test/1
{
    "Start" : "02.05.2017 07:46:49"
}

```

Result:

```
{
  "_index": "textindex",
  "_type": "test",
  "_id": "1",
  "_version": 1,
  "result": "created",
  "_shards": {
    "total": 2,
    "successful": 1,
    "failed": 0
  },
  "created": true
}
```

---

<div class="post-metadata">

**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:** [May 10, 2017, 8:53am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/14 "2017-05-10T08:53:35Z")

</div>

No, don't insert "02.05.2017 07:46:49" in the `Start` field. That'll cause the automapper to pick the wrong format. Delete the index again. Recreate it. Check the mappings. Insert a document with an ISO8601 date (like "2017-05-01T06:34:52.000Z") in the `Start` field. Does that work?

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 10, 2017, 9:08am UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/15 "2017-05-10T09:08:16Z")

</div>

It does.  
This is what the mapping looks like after inserting the document:

```
"testindex": {
    "aliases": {},
    "mappings": {
      "test": {
        "properties": {
          "Start": {
            "type": "date"
          }
        }
      }
    }
```

---

<div class="post-metadata">

**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:** [May 10, 2017, 1:41pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/16 "2017-05-10T13:41:35Z")

</div>

Okay, good. Then the problem must be that the first document you insert with Elasticsearch still has the non-ISO8601 date format, forcing ES's automapper to map the field differently. When a subsequent document with an ISO8601 date arrives it doesn't match the mapping of the field.

---

<div class="post-metadata">

**Author:** ![prime](https://avatars.discourse-cdn.com/v4/letter/p/f1d935/32.png) [@prime](https://discuss.elastic.co/u/prime)\
**Post date:** [May 10, 2017, 2:05pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/17 "2017-05-10T14:05:52Z")

</div>

All date fields in documents i have in the csv fileshave the same format ("02.05.2017 07:46:49").  
Could the problem be caused by the csv headline?

Edit: Seems not to. I deleted the headline and have the same result. Here's an example of the csv file i'm using, the second field is the Start-Field:  
`"12345";"30.04.2017 17:28:09";"01.05.2017 03:25:36";"111.111.111.110";"de";"NSPlayer/12.0.7601.17514";"35847"`

But if i understand correctly, the problem should occure if i don't use the date filter too? That's not the case.

---

<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:** [June 7, 2017, 2:08pm UTC](https://discuss.elastic.co/t/problem-with-the-date-filter-timezone/85145/18 "2017-06-07T14:08:42Z")

</div>

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