# Logstash logs :sql\_last\_value in local timezone, making fresh db records lookup unfeasible **URL:** \ **Category:** Logstash\ **Created:** [July 18, 2018, 5:52pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597 "2018-07-18T17:52:14Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 18, 2018, 5:52pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/1 "2018-07-18T17:52:14Z")
Hello! My goal is to set up continuous data migration from MySQL to Elasticsearch. I've got stuck with `:sql_last_value`. Logstash logs it in local timezone, so next time it queries db, `:sql_last_value` value is wrong. My local timezone is `(CEST, +02:00)`. So if the last received datetime field from db was like `2018-07-20T00:57:34.000Z`, next query to db will be for `:sql_last_value = 2018-07-20 02:57:34` and it won't get any of recently updated records. How can this be solved? Thanks. My logstash.conf: ``` input { jdbc { jdbc_driver_library => "/local/path/mysql-connector-java-8.0.11.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://localhost:3306/db" jdbc_user => "root" jdbc_password => "" schedule => "* * * * *" statement => "SELECT * FROM table WHERE updatedAt > :sql_last_value ORDER BY updatedAt" use_column_value => true tracking_column => "updatedat" tracking_column_type => "timestamp" } } output { stdout { codec => json_lines } elasticsearch { "hosts" => "localhost:9200" "index" => "test-migrate" "document_type" => "data" "document_id" => "%{id}" } } ``` ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 20, 2018, 3:58pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/3 "2018-07-20T15:58:55Z")
`sql_last_value` is always shifted to local timezone in `.logstash_jdbc_last_run`, so in next db query `:sql_last_value` doesn't match actual db datetime records. The only way I found to make scheduled incremental updates working is to set my local timezone to `+00:00`. But it's not an appropriate solution though. ---
**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:** [July 20, 2018, 4:09pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/4 "2018-07-20T16:09:14Z")
Does the [jdbc\_default\_timezone](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_default_timezone) option help you? ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 20, 2018, 4:19pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/5 "2018-07-20T16:19:19Z")
Nope, it converts fetched dates into given timezone. But I need to have the real last fetched timestamp value in my query to db in order to make incremental updates working. ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 20, 2018, 5:58pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/6 "2018-07-20T17:58:08Z")
Wonder how is [this](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_predefined_parameters) supposed to work, if it changes `sql_last_value` to local timezone between queries! But since the configuration file is written in Ruby, maybe I can manually recover the `sql_last_value` value before querying db? ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 25, 2018, 4:19pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/7 "2018-07-25T16:19:30Z")
Posted this issue on [logstash-input-jdbc github](https://github.com/logstash-plugins/logstash-input-jdbc/issues/293), still no answer. Could anyone please help me with this? Maybe there is a possibility to correct `sql_last_value` by manually shifting it back for given number of hours in `logstash.conf` before making `statement` request? ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 26, 2018, 9:34am UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/8 "2018-07-26T09:34:14Z")
Afraid to seem boring, but this bothers me a lot. It's obvious that described in [docs](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state) Logstash jdbc plugin behaviour is implemented improperly. Just if I could do something like ``` ruby { code => "sql_last_value = sql_last_value - 2.hours}" } ``` inside of input plugin, or something like ``` parameters => {"corr_sql_last_value" => sql_last_value - 2.hours} ``` But the only solution I've currently succeeded to find is to set local timezone to UTC. ---
**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\ **Post date:** [July 30, 2018, 10:18pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/9 "2018-07-30T22:18:51Z")
This setting [jdbc\_default\_timezone](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_default_timezone) works like this: - I have a database and all its timestamps are in local time - Logstash/Elasticsearch/Kibana timestamps are always UTC - my local time is +1:00 off of UTC, meaning a db time of 2pm is 1pm UTC - I set this setting to an appropriate timezone string, e.g. "Europe/Paris" - the plugin will convert any date/time/timestamp values into UTC when it is **receiving** data - the plugin will convert any date/time/timestamp values int local time when it is **sending** data. - the plugin stores the sql\_last\_value as UTC - my Logstash event timestamps are in UTC - at the next run, when the sql\_last\_value is used in the SQL statement, the plugin converts the sql\_last\_value timestamp into "Europe/Paris" local time before it inserts it in the statement. - my sql\_last\_value has 1pm but on my db server the logged statement shows 2pm. ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 31, 2018, 3:16pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/10 "2018-07-31T15:16:30Z")
Thanks for detailed explanation! I have tried setting the `jdbc_default_timezone`. My local timezone is `+0200`: ``` ~$ timedatectl status | grep "Time zone" Time zone: Europe/Belgrade (CEST, +0200) ``` So I've set: ``` jdbc_default_timezone => "Etc/GMT-2" ``` My last received date/time value is `2018-07-20T00:57:34.000Z`. It is its exact value in db, and it is what i've got in Elasticsearch index. ``` mysql> select `updatedat` from `table` where `id` = 2; +---------------------+ | updatedat | +---------------------+ | 2018-07-20 00:57:34 | +---------------------+ 1 row in set (0,01 sec) ``` So far so good. But on next db querying `sql_last_value` was `2018-07-20 02:57:34` again. Two hours shifted. And it's stored with the same value: ``` /usr/share/logstash$ cat .logstash_jdbc_last_run --- !ruby/object:DateTime '2018-07-20 02:57:34.000000000 +02:00' ``` I've even tried: ``` jdbc_default_timezone => "Etc/GMT-10" ``` And got `sql_last_value` as `2018-07-20 02:57:34` in SQL statement again... And in `.logstash_jdbc_last_run`: ``` /usr/share/logstash$ cat .logstash_jdbc_last_run --- !ruby/object:DateTime '2018-07-20 02:57:34.000000000 +10:00' ``` It's obviously the `sql_last_value` value in actual db query doesn't depend on `jdbc_default_timezone` setting. So I've set my timezone to `Asia/Shanghai (CST, +0800)`. And got `sql_last_value` = `2018-07-20 08:57:34`. Eight hours shifted. Isn't it weird? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 28, 2018, 3:16pm UTC](https://discuss.elastic.co/t/logstash-logs-sql-last-value-in-local-timezone-making-fresh-db-records-lookup-unfeasible/140597/11 "2018-08-28T15:16:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.