Why is logstash throwing error of daylight saving

Question

We are using LogStash version 7.3.2 to fetch SQL Server data.

And it is working fine but sometimes it is throwing below exception:

Exception when executing JDBC query {:exception=>#
transition (daylight savings time 'gap'): 1942-09-01T00:00:00.000 (Asia/Kolkata)>}

When I check in SQL server then there is no value like 1942-09-01T00:00:00.000.

My LogStash config is as below:

        jdbc_connection_string => "jdbc:sqlserver://HOST:PORT;databaseName=DB_NAME;integratedSecurity=false

        jdbc_user => "USERNAME"
        jdbc_password => "PASSWORD"
        jdbc_driver_library => "/home/user/LOGSTASH/mssql-jdbc-7.4.1.jre8.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        statement => "SELECT * FROM TABLE_NAME  where   INCR_COLUMN  >  :sql_last_value  "
        schedule => "*/60 * * * * *"
        use_column_value => true
        tracking_column => "INCR_COLUMN"
        tracking_column_type => "timestamp"
        record_last_run => true
        last_run_metadata_path => "/home/user/LOGSTASH/LAST_RUN/.mssql_USERS_logstash_jdbc_last_run"
        connection_retry_attempts => "1000"
        connection_retry_attempts_wait_time => "60"
        jdbc_default_timezone => "UTC"

Note: In SQL Server INCR_COLUMN is defined as DateTime.

Any help regarding this would be highly appreciated.

Answer 1

In Logstash if a datetime string has no TZ part it is assumed to be a UTC time.

If you know that the datetime is a local time string then you need to tell the date filter which timezone the date was generated in. You may use one of the Joda timezones,

e.g. America/New_York, America/Detroit or EST5EDT - these are merely labels that point to Timezone Java code that know what changes in clocks occurred in all timezones for quite some time into the past.

See this page for info on how the updates are followed. http://www.oracle.com/technetwork/java/javase/tzdata-versions-138805.html 100

Once the datetime string is converted to a Timestamp object (by the date filter) it is considered UTC time.