Logstash jdbc_default_timezone issue (bug)

Elastic Stack Logstash
1

Hi,

I am using the logstash jdbc input but have problems using the jdbc_default_timezone.
The database I use has timezone Europe/Amsterdam so I thought if I set that as de defaut timezone it would work like a charm.

jdbc {
        #here are the connection stuff for oracle
        
        statement => "SELECT COL_1 DATE_TIME_COL FROM TABLE WHERE DATE_TIME_COL > :sql_last_value

        schedule => "* * * * *"
		clean_run => "false"
		record_last_run => "true"
		last_run_metadata_path => "C:\dev\elk\logstash-5.4.0\filepath\test"
		tracking_column_type => "timestamp"
		jdbc_default_timezone => "Europe/Amsterdam"
     }

I have a record a table with DATE_TIME_COL 2017-08-19 12:19:00.123000

In the logging of logstash I see the following appended for sql_last_value.
When using Europe/Amsterdam: DATE_TIME_COL > TIMESTAMP '2017-08-18 14:18:00.123000 +02:00'
When I comment out the jdbc_default_timezone: DATE_TIME_COL > TIMESTAMP '2017-08-18 12:18:00.276000 +00:00'

When I copy-paste the generated sql and execute that in oracle I do not get any results.

In the case where I use timezone Europe/Amsterdam and I edit the sql and set the hours back two hours to 12:18 I get the expected result.
In the case where I commented out the jdbc_default_timezone, and I edit the sql and set the hours back two hours to 10:18 I get the expected result.

Any ideas where I am going wrong?

Regards Benny

2

Is it not that logstash is giving back the wrong time?

Timezone Europe/Amsterdam is UTC + 2 hours.

If UTC time as defined in this example is 12:18 then it should be replacing last_sql_value with: TIMESTAMP '2017-08-18 12:18:00.276000 +02:00' instead of TIMESTAMP '2017-08-18 14:18:00.123000 +02:00'

A workaround to this issue, if it is an issue, would be to subtract +2:00: WHERE DATE_TIME_COL > :sql_last_value - 2/24

Regards benny

3

Please let me know if I'm jumping to wrong conclusions with my workaround!

Regards

4

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