Double convert timezone when parse datetime from sql

Elastic Stack Logstash
1

I have a table with such columns:

|utcDateTime|hash|Type|tran|Code|
|2019-04-19 14:53:36.000|d893b735ed52413d9b3ac2109961463f|NULL|NULL|NULL|

When I filter them through logstash with jdbc {} input plugin, the first column(utcDateTime) is decreases by another 3 hours (I'm at the UTC+3). And output looks like:
{"@version":"1","utcdatetime":"2019-04-19T11:53:36.000Z","@timestamp":"2019-04-19T11:53:36.000Z"}

Config:

filter {
	mutate {
		convert => {"utcdatetime" => "string"}
	}
	date {
 		match => ["utcdatetime", "ISO8601"]
	}
}

What i did wrong?

2

You might be able to fix it like this. Or you could add a timezone option to the date filter to force it to move the timestamp forward three hours.

3

Unfortunately. It's not work.
I tried:

  1. Add jdbc_default_timezone => "UTC" to input part.
  2. Add timezone => "Europe/Moscow" in filter part.
  3. Example with useTimezone=true&useLegacyDatetimeCode=false&serverTimezone=UTC not work on MSSQL.
  4. Tried all options in same time.

Example:

input {
	jdbc {
		statement => "SELECT [utcDateTime] FROM [dbo].[LogRecords] WHERE utcDateTime > '2019/22/04 7:00' AND error IS NOT NULL"
		jdbc_driver_library => "E:\logstash-6.7.1\sqljdbc_7.2\rus\mssql-jdbc-7.2.2.jre8.jar"
		jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
		jdbc_connection_string => "jdbc:sqlserver://192.168.2.20:1433;databaseName=LogIndex;integratedSecurity=false"
		jdbc_default_timezone => "Europe/Moscow"
		last_run_metadata_path => "E:\logstash-6.7.1\last_run.metadata"
  }
}
filter {
	mutate {
		convert => {"utcdatetime" => "string"}
		remove_field => [
			"[@version]"
				]
	}
	date {
 		match => ["utcdatetime", "ISO8601"]
		timezone => "Europe/Moscow"
	}
}
output {
	stdout{}
}

Output:

{
    "utcdatetime" => "2019-04-22T04:00:18.000Z",
     "@timestamp" => 2019-04-22T04:00:18.000Z
}

When a SQL record time 7:00:18 and realTime 10:00:18

4

My fault.
With line jdbc_default_timezone => "UTC" in input works.

5

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