How to convert my date field to my local timezone

[OphyTe]

To be perfectly honest, I'm not really sure to understand my problem properly

So here it is. I get events from an Oracle database (with jdbc input). When I query this database (with SQL Developer), I get the date in my locale GMT+2 timezone (I presume) - e.g. 02/10/18 16:11:05,502000000.

My ELK stack is in UTC

bash-4.2$ ls -l /etc/localtime
lrwxrwxrwx 1 root root 25 Aug  4 22:05 /etc/localtime -> ../usr/share/zoneinfo/UTC

And so, when I get my events in elastic, the conversion is not made, I get 2018-10-02T16:11:05.502Z (which makes 18:11 in my timezone).

So I try to force my timezone with the Date filter:

date {
  match => [ "my_date", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'" ]
  timezone => "Europe/Paris"
  target => "my_date_paris"
}

... but I get _dateparsefailure errors!

Here is my field mapping:

{
    "task": {
        "mappings": {
            "_doc": {
                "properties": {
                    "my_date": {
                        "type": "date"
                    }
                }
            }
        }
    }
}

So what do I do wrong?

[OphyTe]

With ISO8601 format it doesn't work better by the way ...

date {
  match => [ "my_date", "ISO8601" ]
  timezone => "Europe/Paris"
  target => "my_date_paris"
}

[OphyTe]

I finally succeeded to solve it !

It came from the jdbc input plugin which doesn't get the timezone info from the database. As explain in the plugin documentation :

SQL does not allow for timezone data in timestamp fields. This plugin will automatically convert your SQL timestamp fields to Logstash timestamps, in relative UTC time in ISO8601 format

So I just had to set the jdbc_default_timezone plugin option and it manage the offset :

input {
  jdbc {
    jdbc_driver_library => "/bin/ojdbc7.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@//db_host_name:db_port/service_name"
    jdbc_user => "my_user"
    jdbc_password => "user_password"
    jdbc_default_timezone => "Europe/Paris"
    statement => "select my_date from my_table"
  }
}