# How to convert my date field to my local timezone

**URL:** <https://discuss.elastic.co/t/how-to-convert-my-date-field-to-my-local-timezone/152263>\
**Category:** Logstash\
**Created:** [October 12, 2018, 3:15pm UTC](https://discuss.elastic.co/t/how-to-convert-my-date-field-to-my-local-timezone/152263 "2018-10-12T15:15:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![OphyTe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ophyte/32/36444_2.png) [@OphyTe](https://discuss.elastic.co/u/OphyTe)\
**Post date:** [October 12, 2018, 3:15pm UTC](https://discuss.elastic.co/t/how-to-convert-my-date-field-to-my-local-timezone/152263/1 "2018-10-12T15:15:36Z")

</div>

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)](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923). 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? 😤 😭 🤪

---

<div class="post-metadata">

**Author:** ![OphyTe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ophyte/32/36444_2.png) [@OphyTe](https://discuss.elastic.co/u/OphyTe)\
**Post date:** [October 12, 2018, 3:48pm UTC](https://discuss.elastic.co/t/how-to-convert-my-date-field-to-my-local-timezone/152263/2 "2018-10-12T15:48:18Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![OphyTe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ophyte/32/36444_2.png) [@OphyTe](https://discuss.elastic.co/u/OphyTe)\
**Post date:** [October 16, 2018, 8:28am UTC](https://discuss.elastic.co/t/how-to-convert-my-date-field-to-my-local-timezone/152263/3 "2018-10-16T08:28:21Z")

</div>

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](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_default_timezone) :

> 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"
  }
}

```

---

<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:** [November 13, 2018, 8:28am UTC](https://discuss.elastic.co/t/how-to-convert-my-date-field-to-my-local-timezone/152263/4 "2018-11-13T08:28:24Z")

</div>

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