为什么Logstash与SQL Server数据一起引发夏时制时间间隔错误 [英] Why is logstash throwing error of daylight saving time gap with SQL Server data

问题描述

我们正在使用LogStash版本7.3.2来获取SQL Server数据.

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)>}

当我签入SQL Server时，没有像1942-09-01T00:00:00.000这样的值.

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

我的LogStash配置如下:

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"

注意:在SQL Server中，INCR_COLUMN被定义为DateTime.

Note: In SQL Server INCR_COLUMN is defined as DateTime.

任何对此的帮助将不胜感激.

Any help regarding this would be highly appreciated.

推荐答案

在 Logstash 中，如果datetime字符串不包含TZ part，则假定它是UTC time.

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

如果您知道datetime是本地时间字符串，则需要告诉日期过滤器生成日期的时区.您可以使用Joda时区之一，

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,

例如America/New_York，America/Detroit或EST5EDT-这些只是指向时区Java代码的标签，这些代码知道过去很长时间内所有时区中的时钟发生了什么变化.

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.

有关如何遵循更新的信息，请参见此页面. http://www.oracle.com/technetwork/java/javase/tzdata-versions-138805.html 100

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

将datetime字符串转换为Timestamp对象(通过日期过滤器)后，将其视为UTC time.

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

这篇关于为什么Logstash与SQL Server数据一起引发夏时制时间间隔错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！