# Stuck with timezone issue in logstash JDBC input **URL:** \ **Category:** Logstash\ **Created:** [June 11, 2020, 1:06pm UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707 "2020-06-11T13:06:54Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [June 11, 2020, 1:06pm UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/1 "2020-06-11T13:06:54Z")
I am a bit lost here. Situation: Some transaction happens in Sydney. And the record is stored in SQL database. The time stored is in UTC. 5 mins later, we pull that data using Logstash JDBC input. Kibana pulls up and shows the latest data. And we end up with a discovery panel where the events are shown trailing by 11 hours. Our end customers are wondering how something which happened now is shown as to have happened 11 hrs ago on discovery panel. This is sample of the data I get from the database: 2020-06-03 21:19:41.783 I thought of leaving the original field as it is and adding a new field. And then using that field as the source of time when creating the index pattern. This is what I set in Logstash in Ruby filter. ``` { code => "event.set('localdateexp', event.get('createdon').time.localtime.strftime('%Y-%m-%dT%H:%M:%S.%3N%z'))" } ``` But I did not create a new index pattern since in the discovey panel the new field `localdateexp` looked the same as `createdon` field. ![screen](https://us1.discourse-cdn.com/elastic/original/3X/e/0/e0b2cf0ae44ac3871b4945f5294776af9af199d5.jpeg) I do not want to change the Kibana timezone setting from Browser to UTC. There are other indices which get data direct from applications rather than from a database. Any ideas? ---
**Author:** ![Jenni](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jenni/32/29684_2.png) [@Jenni](https://discuss.elastic.co/u/Jenni)\ **Post date:** [June 11, 2020, 1:35pm UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/2 "2020-06-11T13:35:54Z")
I'm a bit confused right now. But let's try to tidy up the chaos in my head: Something happened at 12:37 local time and was imported at 13:00, so 02:37 and 03:00 UTC because Sydney is UTC+10. It's now showing up as 02:37 local time instead which would mean that at some point in your pipeline UTC was interpreted as Sydney time and the JSON representation of your event with the UTC dates says that `createdon` is 16:37 the previous day while `@timestamp` is 03:37 today? Is that right? ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [June 11, 2020, 1:52pm UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/3 "2020-06-11T13:52:37Z")
That is what I am thinking is happening. "UTC was interpreted as Sydney time": I think so since I am not touching the field. Should I be doing something in logstash? To test I introduced that Ruby filter and tried to create a new field from the value of createdon field. But no luck yet. I have read answers and most of them are saying that it is better to leave things in UTC as curator, kibana etc expect UTC only. But this messes up discovery panel. ---
**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:** [June 11, 2020, 2:07pm UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/4 "2020-06-11T14:07:27Z")
You may be able to fix this by setting the jdbc\_default\_timezone option on the jdbc input. Alternatively, configure the [jdbc\_connection\_string](https://discuss.elastic.co/t/error-logstash-inputs-jdbc-unable-to-connect-to-database/177091/4) to tell it what timezone to use. ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [June 15, 2020, 3:23am UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/5 "2020-06-15T03:23:59Z")
Hi @Badger I will be using that setting and updating the thread with results soon. Meanwhile, I have realized that my logstash on linux box is of version 7.7.0 while the ES instance in cloud is 7.7.1. Maybe that might be the reason. Otherwise I see that the createdon field is different for events which happened within an hour difference. "createdon": "2020-06-12T03:46:27.467Z", "createdon": "2020-06-11T17:36:49.950Z", In database explorer I exported the results as csv and opened in notpad++ to get the real data. The format it is coming in is: 2020-06-12 03:46:27.467 2020-06-12 03:36:49.950 There is no trailing Z at the end of the timestamps. Is this something I can look into to process explicitly in Elasticsearch? I can use logstash filters to add zone at the end of the timestamp though I am a bit cagey about it and DST in particular. Sorry these are not the exact events whose timestamp I took out. But I think it is enough to show the difference. ---
**Author:** ![Jenni](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jenni/32/29684_2.png) [@Jenni](https://discuss.elastic.co/u/Jenni)\ **Post date:** [June 15, 2020, 10:21am UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/6 "2020-06-15T10:21:46Z")
If you need further help to find the right timezone settings, it might be helpful to set up a pipeline that only consists of your JDBC input and a rubydebug output and post the results. The Z won't be necessary if Logstash is told which timezone to expect. Your target should be a correct Logstash Timestamp object (that uses UTC), not a specific string format for the date. ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [June 18, 2020, 6:10am UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/7 "2020-06-18T06:10:21Z")
> [@Badger](#): > > jdbc\_default\_timezone @Badger This settings solved my issue. Many thanks. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 16, 2020, 6:10am UTC](https://discuss.elastic.co/t/stuck-with-timezone-issue-in-logstash-jdbc-input/236707/8 "2020-07-16T06:10:25Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.