# Date from DB is changed when inserted in ES

**URL:** <https://discuss.elastic.co/t/date-from-db-is-changed-when-inserted-in-es/285904>\
**Category:** Logstash\
**Created:** [October 5, 2021, 10:59am UTC](https://discuss.elastic.co/t/date-from-db-is-changed-when-inserted-in-es/285904 "2021-10-05T10:59:54Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Blazkowicz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/blazkowicz/32/79589_2.png) [@Blazkowicz](https://discuss.elastic.co/u/Blazkowicz)\
**Post date:** [October 5, 2021, 10:59am UTC](https://discuss.elastic.co/t/date-from-db-is-changed-when-inserted-in-es/285904/1 "2021-10-05T10:59:54Z")

</div>

Hi all,

Having an issue with a date field when trying to insert it in ES. Searched and tried multiple modes but to no avail.  
Short story, my date field from Postgres ends up in ES with a timezone attached/offset, even though my DB is set in UTC.

Long story,

**Postgres DB** :

```auto
timezone = UTC
field_date = date (YYYY-MM-DD)

```

**Logstash** (the server running it is in timezone = JST)

```auto
input {
  jdbc {
     jdbc_connection_string => "jdbc:postgresql://server_name:port/db_name"
     jdbc_user => "user"
     jdbc_password => "pass"
     jdbc_driver_class => "org.postgresql.Driver"
     statement => "select * from function_name()"
     clean_run => true
     last_run_metadata_path => "/tmp/something"
 }
}
filter {
  date {
    match => ["field_date", "yyyy-MM-dd"]
    timezone => "UTC"
    target => "field_date"
 }
}
output {
  elasticsearch {
    hosts => ["http://server:9200"]
    index => "index_smt"
    document_id => "index_smt_%{field1}_%{field2}_%{field_date}"
    doc_as_upsert => true
 }
}

```

When executing my function in the DB i will have something like this:

```auto
field1 | field2 | field_date
test | test | 2021-09-28

```

This is correct and it's what I expect.  
Even when running a psql -c "select \* from function\_name()" from my Logstash server, the result set is the one expected.

When I **check the \_id field in ES** , somehow it's like this:

```auto
index_smt_test_test_2021-09-27T15:00:00.000Z

```

So, somehow the date is offset, clearly with the JST offset, but I don't know where this is applied.

I **would expect that the \_id field** would be:

```auto
index_smt_test_test_2021-09-28

```

Tried using combinations of:

```auto
jdbc_default_timezone => "UTC" / "JST"
plugin_timezone => "local" / "utc"

```

Nothing seem to work, I still get the field\_date column offset and the hour somehow appended to it.

What am I missing here?  
Appreciate any help.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [October 5, 2021, 2:27pm UTC](https://discuss.elastic.co/t/date-from-db-is-changed-when-inserted-in-es/285904/2 "2021-10-05T14:27:01Z")

</div>

The jdbc input will [convert](https://github.com/logstash-plugins/logstash-input-jdbc/blob/878a73eb67b68366fac74fcc65e9cadd6cf44766/lib/logstash/plugin_mixins/jdbc/jdbc.rb#L78) a date or time in the database to a LogStash::Timestamp. The jdbc\_default\_timezone option can be used to tell it to use a timezone that differs from the default for the system where logstash runs.

---

<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 2, 2021, 2:27pm UTC](https://discuss.elastic.co/t/date-from-db-is-changed-when-inserted-in-es/285904/3 "2021-11-02T14:27:53Z")

</div>

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