JDBC Input set jdbc_default_timezone loses milliseconds

[Rob---]

The bug:

1. When setting the timezone for UTC within JDBC input plug, it will lose the millesconds values of the datetime2 field ingested from sql.

For example: 2016-06-07T10:20:05.223Z with no timezone set will convert via the machine's timezone with no data loss. e.g. if the computer was New York time, it would convert by adding +4 to the hours, and maintaining the rest of the data.
However, if you specify that the timezone is UTC by putting jdbc_default_timezone => "UTC" in your input JDBC plugin, it will convert the time to be 2016-06-07T10:20:05.000Z

Setup Info:
Logstash Version: 2.3.2 (current)
OS: Windows 7
Config File:

input {
  jdbc {
    jdbc_driver_library => "xxx\sqljdbc4.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "xxx"
    jdbc_user => "xxx"
    jdbc_password => "xxx"
    statement => "xxx"
    type => "xxx"
    jdbc_default_timezone => "UTC"
  }


}

filter {
}

output {
    stdout {
      codec => json_lines
    }
    elasticsearch{
        index => "alarms"
        document_type => "alarm"            
    }
}

Sample Data:
SQL, pulling data from a datetime2 field.

Steps to reproduce: Ingest data into elasticsearch via the minimal logstash config shown above using the JDBC plugin with the JDBC driver sqljdbc4.jar from a SQL database table that contains a datetime2(7) field.

[artyombaranovskiytr]

+1

[mymasse]

+1

[mbelousov7]

+1

[ajakuszyk]

The truncation still happens on latest logstash 5.2.2 for jdbc_default_timezone => "Europe/Zurich"
As a workaround I've removed jdbc_default_timezone from input and used this filter:

filter {	
	# need to convert to string before changing timezone
	mutate {
		convert => { "timestamp" => "string" }
	}
	
	date {
		match => [ "timestamp", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'" ]
		timezone => "Europe/Zurich"
		remove_field => "timestamp"
	}	
}

[jeslopalo]

It's same for me with 'Europe/Madrid'

[jbwl]

Please fix this, it leads to data duplication which is obviously dangerous.

[bzier]

I specifically encounter this (or something similar) when using a datetime2 field as the tracking_column. The timestamp stored in the last_run_metadata_path has the full precision, but when it is used in the statement as the :sql_last_value, the value sent is truncated to a precision of .fff. In this way it is a little different than what the OP posted because milliseconds aren't lost completely, but the precision is off, and results in duplicated data. If this should be logged as a separate issue, please let me know.

[guyboertje]

OK I now know what the problem is here.

This line converts a DateTime into a Time but drops the sub-seconds in the process.

[mtonpe]

I specifically encounter this (or something similar) when using a datetime2 field as the tracking_column. The timestamp stored in the last_run_metadata_path has the full precision, but when it is used in the statement as the :sql_last_value, the value sent is truncated to a precision of .fff. In this way it is a little different than what the OP posted because milliseconds aren't lost completely, but the precision is off, and results in duplicated data. If this should be logged as a separate issue, please let me know.

Is this been fixed in any version of logstash-jdbc?