Failed parsing date from field creation_date (jdbc)

Elastic Stack Logstash
1

Hi,
I tried to load data from my MySQL database to ES. It worked well, only fields with datetime have a wrong time in it.
As you can see in the config-file below, I used a date filter to solve this with several formats, but it's giving me the exception:

"←[33mFailed parsing date from field {:field=>"creation_date", :value=>"2014-05-21T12:19:32.000Z", :exception=>"cannot convert instance of class org.jruby.RubyObject to class java.lang.String", :config_parsers=>"ISO8601,yyyy-MM-dd
HH:mm:ss", :config_locale=>"en", :level=>:warn}←[0m"

Any idea what's the problem here?

Thanks.

file:db.conf

input {
jdbc {
#MySQL jdbc connection string to our database, XY
jdbc_connection_string => "jdbc:mysql://XY"

#The user we wish to execute our statement as
jdbc_user => XY

#The user password
jdbc_password => XY

#The path to our downloaded jdbc driver
jdbc_driver_library => "C:\ES_b_dev\2016_04_04\elasticsearch-jdbc-2.3.1.0\lib\mysql-connector-java-5.1.38.jar"

#The name of the driver class for MySQL
jdbc_driver_class => "com.mysql.jdbc.Driver"

#Our query
statement => XY
}
}

filter {
date {
timezone => "Europe/Berlin"
match => ["creation_date" , "ISO8601", "yyyy-MM-dd HH:mm:ss"]
#match => ["preset_start_date" , "yyyy-MM-dd HH:mm:ss"], "yyyy-MM-ddTHH:mm:ss.SSSZ"
#match => ["preset_start_time" , "yyyy-MM-dd HH:mm:ss"]
#match => ["start_time" , "yyyy-MM-dd HH:mm:ss"]
#match => ["end_time" , "yyyy-MM-dd HH:mm:ss"]
#timezone => "UTC"
#target => "creation_date"
}
}

output {
#stdout { codec => json_lines }
elasticsearch {
#protocol => http
index => "XY"
document_type => "XY"
document_id => "%{XY}"
hosts => "localhost"
}
}

2

Try converting the date to a string before your date filter:

mutate {
     convert => [ "creation_date", "string" ]
 }

date {
    timezone => "Europe/Berlin"
    match => ["creation_date" , "ISO8601", "yyyy-MM-dd HH:mm:ss"]
}
1 Like
3

Thanks for you answer.

The error is gone but the time is still wrong.

The field in my database says 2012-07-17 08:00:36 and in my elasticsearch document it says 2012-07-17T06:00:36.000Z

4

That's expected. ES stores timestamps in UTC and Berlin is two hours ahead of UTC during July.

5

So I have to change my timezone in the presentation layer?

6

Exactly.

7

Thanks!

I've also have a problem with indexing multiple sql tables in one config file. Should I open a new thread or can we continue here?

8

It sounds like a different question so I suggest a new thread.