# How to parse date field into @timestamp

**URL:** <https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396>\
**Category:** Logstash\
**Created:** [November 13, 2017, 1:15pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396 "2017-11-13T13:15:57Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 13, 2017, 1:15pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/1 "2017-11-13T13:15:57Z")

</div>

I'm trying to replace the @timestamp that's generated by logstash with the contents of an existing field in my data. I've been fighting with this all day, and I'm nowhere.

My data looks like this

{  
"start\_time" : "2017-11-09T21:15:51.906Z"  
.  
.  
.  
}

What I need to do is to grab the value in start\_time and put that into the @timestamp field. So I started with the simple stuff, like using mutate to simply replace the value in @timestamp with start\_time. Of course, that fails because @timestamp is a Timestamp and start\_time is a string...

Digging further I read a bunch of stuff on S/O and elsewhere that said I should be able to use the filter:date to parse the date in my string and assign that to @timestamp.

So far nothing that I've tried using the date parsing stuff has worked.

I tried this:

date {  
match =\> ["start\_time", "yyyy-MM-ddTHH:mm:ss.SSSZ", "ISO8601"]  
target =\> @timestamp  
}

This gives me an error before logstash even gets running:

Cannot load an invalid configuration {:reason=\>"Illegal pattern  
component: T"}

So then I tried:

date {  
match =\> ["start\_time", "%{TIMESTAMP\_ISO8601}"]  
target =\> "@timestamp"  
}

I get the same response from this.

Finally, I tried:

date {  
match =\> ["start\_time", "ISO8601"]  
target =\> "@timestamp"  
}

This one at least allows logstash to start, but when an event is delivered, I get a resulting payload containing

"tags":["\_dateparsefailure"]

No other stack trace or anything, just that...

Can someone point me in the right direction?

Edit: After more searching I tried the following (allowing for the T char in my date string):

date {  
match =\> ["start\_time", "yyyy-MM-dd'T'HH:mm:ss.SSSZ", "ISO8601"]  
target =\> "@timestamp"  
}

Unfortunately, while this starts up ok, I still get the \_dateparsefailure tag.

---

<div class="post-metadata">

**Author:** ![paz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/paz/32/28003_2.png) [@paz](https://discuss.elastic.co/u/paz)\
**Post date:** [November 13, 2017, 2:18pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/2 "2017-11-13T14:18:09Z")

</div>

You also need to quote the millisecond dot, like this:

```auto
date {
    match => ["start_time", "yyyy-MM-dd'T'HH:mm:ss'.'SSSZ"]
    target => "@timestamp"
}
```

Also, you do not need the second _"ISO8601"_ pattern if you only have a specific datetime pattern.

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 13, 2017, 2:27pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/3 "2017-11-13T14:27:17Z")

</div>

That doesn't seem to change anything, unfortunately. I still get the \_dateparsefaileure tag and the date is not properly set into @timestamp.

---

<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:** [November 13, 2017, 2:45pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/4 "2017-11-13T14:45:20Z")

</div>

Just using ISO8601 doesn't work? `Z` is also a pattern character in these strings so you need `'Z'`.

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 13, 2017, 2:50pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/5 "2017-11-13T14:50:06Z")

</div>

Using ISO8601 gives the same result. As does using my original string with the Z in single quotes.

---

<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:** [November 13, 2017, 3:06pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/6 "2017-11-13T15:06:13Z")

</div>

That's surprising. What's in the Logstash log? The date filter logs any failures.

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 13, 2017, 3:11pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/7 "2017-11-13T15:11:26Z")

</div>

I was expecting that the logs would show something, but they don't. There's no error or stacktrace or anything from the date filter. The log contents of one complete run of my test is as follows:

```
[2017-11-13T10:07:44,154][INFO][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[2017-11-13T10:07:44,183][INFO][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://localhost:9200/
, :path=>"/"}
[2017-11-13T10:07:44,350][WARN][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>#<URI::HTTP:0x6919c553 URL:http://localhost:9200/>}
[2017-11-13T10:07:44,351][INFO][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2017-11-13T10:07:44,417][INFO][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>50001, "settings"=>{"in
dex.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_map
ping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false
, "fields"=>{"keyword"=>{"type"=>"keyword"}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>fals
e}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}
}}}}}
[2017-11-13T10:07:44,431][INFO][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>[#<URI::HTTP:0x6ae35e90 URL:http:
//localhost:9200>]}
[2017-11-13T10:07:44,434][INFO][logstash.pipeline] Starting pipeline {"id"=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipe
line.max_inflight"=>250}
[2017-11-13T10:07:45,399][INFO][logstash.pipeline] Pipeline main started
[2017-11-13T10:07:45,509][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=>9600}
[2017-11-13T10:08:00,346][INFO][logstash.inputs.jdbc] (0.041000s) SELECT * from DS_AUDIT where START_TIME > TIMESTAMP '1970-01-01 00:00:00.000000 +00:00'
[2017-11-13T10:08:18,699][WARN][logstash.runner] SIGINT received. Shutting down the agent.
[2017-11-13T10:08:18,715][WARN][logstash.agent] stopping pipeline {:id=>"main"}

```

Strangely, the log doesn't show the output from the 1 record that gets extraced from the database, although it does show on stdout.

---

<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:** [November 13, 2017, 5:09pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/8 "2017-11-13T17:09:50Z")

</div>

Okay. Please show get you get on stdout and your configuration so that we can attempt to reproduce.

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 13, 2017, 5:19pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/9 "2017-11-13T17:19:03Z")

</div>

The config looks like this:

```
 input {
    jdbc {
        jdbc_connection_string => "jdbc:oracle:thin:@192.168.56.101:1521/MRO"
        jdbc_user => "user"
        jdbc_password => "password"
        jdbc_driver_library => "/vagrant/chef-repo/cookbooks/logstash/files/ojdbc14-10.2.0.1.0.jar" 
        jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
        statement => "SELECT * from DS_AUDIT where START_TIME > :sql_last_value"
        schedule => "* * * * *"
        clean_run => "true"
        tracking_column => "START_TIME"
        tracking_column_type => "timestamp"
    }
}
filter {
  date {
    match => ["start_time", "yyyy-MM-dd'T'HH:mm:ss'.'SSS'Z'"]
    target => "@timestamp"
  }
}
output {
    elasticsearch {
        index => "sites"
        document_type => "site"
        hosts => "http://localhost:9200"
    }
}
output {
    stdout { codec => json_lines }
}

```

When I run my test, I get the following on stdout:

```
[2017-11-13T12:15:47,594][INFO][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[2017-11-13T12:15:47,609][INFO][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://localhost:9200/, :path=>"/"}
[2017-11-13T12:15:47,843][WARN][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>#<URI::HTTP:0x6090de40 URL:http://localhost:9200/>}
[2017-11-13T12:15:47,845][INFO][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2017-11-13T12:15:47,924][INFO][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>50001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword"}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2017-11-13T12:15:47,935][INFO][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>[#<URI::HTTP:0x70b3004c URL:http://localhost:9200>]}
[2017-11-13T12:15:47,942][INFO][logstash.pipeline] Starting pipeline {"id"=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>250}
[2017-11-13T12:15:49,436][INFO][logstash.pipeline] Pipeline main started
[2017-11-13T12:15:49,569][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=>9600}
[2017-11-13T12:16:00,579][INFO][logstash.inputs.jdbc] (0.107000s) SELECT * from DS_AUDIT where START_TIME > TIMESTAMP '1970-01-01 00:00:00.000000 +00:00'
{"user_name":null,"end_time":null,"audit_status":null,"tags":["_dateparsefailure"],"start_time":"2017-11-09T21:15:51.906Z","site":null,"@timestamp":"2017-11-13T17:16:00.676Z","application":"Interactive","method_name":null,"@version":"1","details":null,"method_class":null,"status":null}
^C[2017-11-13T12:16:30,048][WARN][logstash.runner] SIGINT received. Shutting down the agent.
[2017-11-13T12:16:30,060][WARN][logstash.agent] stopping pipeline {:id=>"main"}

```

The line that I get after the JDBC SELECT statement in the stdout, does not show up in the log file. I'm assuming that that is simply due to the fact the I have a stdout output stage in my pipeline.

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 13, 2017, 7:02pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/10 "2017-11-13T19:02:45Z")

</div>

An interesting development. I tried the following:

```
filter {
  mutate {
    convert => { "start_time" => "string" }
  }
  date {
    match => ["start_time", "yyyy-MM-dd'T'HH:mm:ss'.'SSS'Z'"]
    timezone => "UCT"
    target => "@timestamp"
  }
}

```

And it works. My assumption here is that since start\_time is coming from a JDBC call its type is not actually a String, so the date's matcher fails in a weird fashion. Converting it to a String first seems to do the trick.

---

<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:** [November 13, 2017, 8:44pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/11 "2017-11-13T20:44:08Z")

</div>

> My assumption here is that since start\_time is coming from a JDBC call its type is not actually a String, so the date's matcher fails in a weird fashion. Converting it to a String first seems to do the trick.

Yes, that's exactly right. I filed this issue a while back:

> <https://github.com/logstash-plugins/logstash-filter-date/issues/95>
>
> When using the jdbc input to fetch events from a database, timestamp fields end …up as timestamp fields in Logstash. One could argue that this is a feature, but it causes confusion since those fields apparently can't be processed by the date filter. Could we either call to\_s on the source string or check if the source already is a timestamp and, if so, just copy it to the destination field?
> 
> See https://discuss.elastic.co/t/trouble-matching-timestamp/83768 for an example.

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 13, 2017, 9:56pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/12 "2017-11-13T21:56:26Z")

</div>

So then would the recommended approach to this be to use the mutate filter to create a new string field and convert the @timestamp from that? Or would it be better to just do it in a Ruby filter instead?

---

<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:** [November 14, 2017, 6:11am UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/13 "2017-11-14T06:11:36Z")

</div>

Why not just rename the field since it already is a timestamp?

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 14, 2017, 12:46pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/14 "2017-11-14T12:46:21Z")

</div>

Unfortunately, I don't control the schema for elasticsearch. Other users of the system rely on the start\_time field as well, so it needs to remain

---

<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:** [November 14, 2017, 1:29pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/15 "2017-11-14T13:29:50Z")

</div>

So do you need to do anything at all then? Wasn't your end goal to have `start_time` as a date field?

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 14, 2017, 1:47pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/16 "2017-11-14T13:47:54Z")

</div>

No, the end goal was to have @timestamp have the same value as start\_time. I think I have a solution that will work based on what we've discussed here. Thanks for all of your help...

---

<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:** [November 14, 2017, 2:06pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/17 "2017-11-14T14:06:58Z")

</div>

Okay. How about copying `start_time` to `@timestamp` with the mutate filter's copy option?

---

<div class="post-metadata">

**Author:** ![sdussin](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@sdussin](https://discuss.elastic.co/u/sdussin)\
**Post date:** [November 14, 2017, 7:26pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/18 "2017-11-14T19:26:26Z")

</div>

The version of logstash that is currently approved for use here (v5.3.1) doesn't seem to have a copy method. I tried replace originally but that fails with a type mismatch.

---

<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:** [December 12, 2017, 7:26pm UTC](https://discuss.elastic.co/t/how-to-parse-date-field-into-timestamp/107396/19 "2017-12-12T19:26:58Z")

</div>

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