# ISO8601 to epoch seconds
**URL:** https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033
**Category:** Logstash
**Created:** [February 12, 2020, 3:33pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033 "2020-02-12T15:33:13Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@fim](https://discuss.elastic.co/u/fim)
#### Post date: [February 12, 2020, 3:33pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/1 "2020-02-12T15:33:14Z")
Hi
I ingest data from a mysql database and in a specific column there are date/time in ISO8601 format stored, which I would like to convert as @timestamp in elasticsearch.
I followed the question here: [Convert @timestamp to epoch](https://discuss.elastic.co/t/convert-timestamp-to-epoch/64522/2)
Unfortunaltely it didn't work.
Filter section of my logstash pipeline looks as follows:
```
filter {
remove_field => ["@timestamp"]
}
ruby {
code => "event.set('@timestamp', event.get('date_time').to_i)"
}
}
```
But I always get following log entries:
`[2020-02-12T16:20:04,562][ERROR][logstash.filters.ruby][sagsys-vpos] Ruby exception occurred: wrong argument type Integer (expected LogStash::Timestamp)`
How can I get rid of the expection?
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [February 12, 2020, 4:14pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/2 "2020-02-12T16:14:29Z")
Use a date filter instead of a ruby filter.
---
### Author:  [@fim](https://discuss.elastic.co/u/fim)
#### Post date: [February 12, 2020, 4:59pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/3 "2020-02-12T16:59:08Z")
I already tried:
```
date {
match => ["date_time" , "ISO8601"]
target => "@timestamp"
remove_field => ["date_time"]
}
```
error message:
`[2020-02-12T17:57:39,909][WARN][logstash.outputs.elasticsearch][sagsys-vpos] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"sagsys-vpos", :_type=>"_doc", :routing=>nil}, #], :response=>{"index"=>{"_index"=>"connect-sagsys-vpos", "_type"=>"_doc", "_id"=>"kDdVOnABi2wWiksar-uX", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [@timestamp] of type [date] in document with id 'kDdVOnABi2wWiksar-uX'. Preview of field's value: '2020-02-12T16:57:37.874Z'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [2020-02-12T16:57:37.874Z] with format [epoch_second]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"date_time_parse_exception: Failed to parse with all enclosed parsers"}}}}}}`
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [February 12, 2020, 10:19pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/4 "2020-02-12T22:19:54Z")
> [@fim](#):
>
> "reason"=\>"failed to parse date field [2020-02-12T16:57:37.874Z] with format [epoch\_second]"
OK, so you must have an index template that says your @timestamp is formatted as epoch\_second. That's not what logstash is going to send. so elasticsearch is not going to be able to parse what it receives.
---
### Author:  [@fim](https://discuss.elastic.co/u/fim)
#### Post date: [February 13, 2020, 6:59am UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/5 "2020-02-13T06:59:07Z")
This is also already specified. The @timestamp field as epoch\_seconds in the index template.
```
"mappings" : {
"properties" : {
"@timestamp" : {
"format" : "epoch_second",
"type" : "date"
}
}
}
```
---
### Author:  [@Sukanya\_Arumugam1](https://discuss.elastic.co/u/Sukanya_Arumugam1)
#### Post date: [February 13, 2020, 10:02am UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/6 "2020-02-13T10:02:22Z")
Hi,
Can you please try the below approach .
date {
match =\> ["date\_time", "ISO8601"]
target =\> "date\_time"
}
ruby{
code =\>
'
event.set("@timestamp", event.get("date\_time").to\_i)
'
}
---
### Author:  [@fim](https://discuss.elastic.co/u/fim)
#### Post date: [February 13, 2020, 10:47am UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/7 "2020-02-13T10:47:57Z")
Thanks for the hint Sukanya.
I'll tried it without success:
Two error messages in logstash-plain:
```
[2020-02-13T11:44:28,206][WARN][logstash.outputs.elasticsearch][sagsys-vpos] Could not index event to Elasticsearch. {:status=>400, :action=>["inde
x", {:_id=>nil, :_index=>"connect-sagsys-vpos", :_type=>"_doc", :routing=>nil}, #], :response=>{"index"=>{"_index"=>"con
nect-sagsys-vpos", "_type"=>"_doc", "_id"=>"Z5ImPnABLMeXi7w5YBFU", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to
parse field [@timestamp] of type [date] in document with id 'Z5ImPnABLMeXi7w5YBFU'. Preview of field's value: '2020-02-13T10:44:26.396Z'", "caused_
by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [2020-02-13T10:44:26.396Z] with format [epoch_second]", "caused_by
"=>{"type"=>"date_time_parse_exception", "reason"=>"date_time_parse_exception: Failed to parse with all enclosed parsers"}}}}}}
```
`[2020-02-13T11:44:28,228][ERROR][logstash.filters.ruby][sagsys-vpos] Ruby exception occurred: wrong argument type Integer (expected LogStash::Timestamp)`
---
### Author:  [@Sukanya\_Arumugam1](https://discuss.elastic.co/u/Sukanya_Arumugam1)
#### Post date: [February 13, 2020, 11:56am UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/8 "2020-02-13T11:56:38Z")
> [@Sukanya\_Arumugam1](#):
>
> ruby{
> code =\>
> '
> event.set("@timestamp", event.get("date\_time").to\_i)
> '
> }
can you please give me the output of this:
ruby{
code =\>
'
event.set("epoch", event.get("date\_time").to\_i)
'
}
Just to check whether conversion is happening fine or not.
Thanks
---
### Author:  [@fim](https://discuss.elastic.co/u/fim)
#### Post date: [February 13, 2020, 12:55pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/9 "2020-02-13T12:55:59Z")
Tried two things:
1. use an index name, where the above index pattern is specified (@timestamp = format epoch\_milis)
This seems not to work
````
[2020-02-13T13:40:37,604][INFO][logstash.outputs.elasticsearch][sagsys-vpos] Attempting to install template {:manage_template=>{"index_patterns"=>"logstash-*", "version"=>60001,
"settings"=>{"index.refresh_interval"=>"5s", "number_of_shards"=>1}, "mappings"=>{"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", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"ty
pe"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}
[2020-02-13T13:40:37,605][DEBUG][org.logstash.config.ir.CompiledPipeline][sagsys-vpos] Compiled filter
P[filter-ruby{"code"=>"event.set(\"epoch\", event.get(\"date_time\").to_i)"}|[str]pipeline:26:5:```
ruby {
code => 'event.set("epoch", event.get("date_time").to_i)'
}
```]
into
org.logstash.config.ir.compiler.ComputeStepSyntaxElement@311877f2
[2020-02-13T13:40:37,612][INFO][logstash.javapipeline][sagsys-vpos] Pipeline started {"pipeline.id"=>"sagsys-vpos"}
[2020-02-13T13:40:37,616][DEBUG][logstash.outputs.elasticsearch][sagsys-vpos] Found existing Elasticsearch template. Skipping template management {:name=>"logstash"}
[2020-02-13T13:40:37,615][DEBUG][org.logstash.execution.PeriodicFlush][sagsys-vpos] Pushing flush onto pipeline.
[2020-02-13T13:40:37,625][DEBUG][logstash.javapipeline] Pipeline started successfully {:pipeline_id=>"sagsys-vpos", :thread=>"#"}
[2020-02-13T13:40:39,470][WARN][logstash.outputs.elasticsearch][sagsys-vpos] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"connect-sagsys-vpos", :_type=>"_doc", :routing=>nil}, #], :response=>{"index"=>{"_index"=>"connect-sagsys-vpos", "_type"=>"_doc", "_id"=>"36-QPnABLMeXi7w5v7SJ", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [@timestamp] of type [date] in document with id '36-QPnABLMeXi7w5v7SJ'. Preview of field's value: '2020-02-13T12:40:38.156Z'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [2020-02-13T12:40:38.156Z] with format [epoch_second]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"date_time_parse_exception: Failed to parse with all enclosed parsers"}}}}}}
[2020-02-13T13:40:39,472][WARN][logstash.outputs.elasticsearch][sagsys-vpos] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"connect-sagsys-vpos", :_type=>"_doc", :routing=>nil}, #], :response=>{"index"=>{"_index"=>"connect-sagsys-vpos", "_type"=>"_doc", "_id"=>"4K-QPnABLMeXi7w5v7SJ", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [@timestamp] of type [date] in document with id '4K-QPnABLMeXi7w5v7SJ'. Preview of field's value: '2020-02-13T12:40:38.160Z'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [2020-02-13T12:40:38.160Z] with format [epoch_second]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"date_time_parse_exception: Failed to parse with all enclosed parsers"}}}}}}
````
1. Use an index which has no index template specified
This works.
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [February 13, 2020, 1:36pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/10 "2020-02-13T13:36:07Z")
OK, so that's the problem. Remove it.
---
### Author:  [@fim](https://discuss.elastic.co/u/fim)
#### Post date: [February 13, 2020, 2:47pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/11 "2020-02-13T14:47:23Z")
Unfortunately I can't.
elasticsearch and logstash should be able to handle this situation...
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 12, 2020, 2:47pm UTC](https://discuss.elastic.co/t/iso8601-to-epoch-seconds/219033/12 "2020-03-12T14:47:29Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.