Hello,
I have an SQLite database where new data is written every second. Currently, I am using Logstash and its JDBC plugin to read the data in real-time and ship it to Elasticsearch for visualization. Everything is working great, except for one strange issue. One of the columns in this SQLite database is called "Time", containing the datetime value of when the event occurred. For some reason, Logstash seems to be overwriting the time portion of all these values as 04:00:00:000Z (e.g. If the value of the column is "2011-07-16 10:00:11" then Logstash outputs "2011-07-16T04:00:00.000Z"). This occurs for every row, every single time. I can not figure out why this is happening, or even stranger, why it seems to default the hour to 04.
Some things to help with troubleshooting...
Versions and OS
Logstash 7.15.1
Windows 10
My Logstash configuration file
input {
jdbc {
jdbc_connection_string => "jdbc:sqlite:C:\Analysis_Int\EventExport.db3"
jdbc_driver_library => "C:\tmp\sqlite-jdbc-3.36.0.3.jar"
jdbc_driver_class => "org.sqlite.JDBC"
jdbc_user => ""
statement => "SELECT * FROM FuelConsumed WHERE ID > :sql_last_value"
schedule => "*/5 * * * * *"
use_column_value => true
tracking_column_type => "numeric"
tracking_column => "id"
last_run_metadata_path => "C:\tmp\logstash-7.15.1\last_run_metadata.log"
}
}
output {
stdout {
}
}
SQLite column names and datatypes
|ID|4|INTEGER|
|---|---|---|
|TimelineID|12|TEXT|
*|Time|12|DATETIME|*
|UnitID|12|TEXT|
|UnitName|12|TEXT|
|UnitClass|12|TEXT|
|UnitSide|12|TEXT|
|FuelQuantity|6|DOUBLE|
|FuelType|4|INTEGER|
|MiscInfo|12|TEXT|
Another example of how the values of database differ from Logstash and JDBC output
In SQL
{
"fuelquantity" => 0.001666667,
"@version" => "1",
"unitname" => "S 31 Sabalo",
"unitside" => "Venezuela",
"fueltype" => 4001,
"unitclass" => "S 31 Sabalo [Type 209-1300]",
*"time" => 2011-07-16 10:04:10,*
"@timestamp" => 2021-11-03T17:18:20.287Z,
"timelineid" => "14654039-c619-4bd5-8ac4-6988c31ad87e",
"id" => 71908,
"miscinfo" => nil,
"unitid" => "8813afed-2d28-4f14-ae3b-3e0c6bb3f780"
}
Logstash and JDBC Output
{
"fuelquantity" => 0.001666667,
"@version" => "1",
"unitname" => "S 31 Sabalo",
"unitside" => "Venezuela",
"fueltype" => 4001,
"unitclass" => "S 31 Sabalo [Type 209-1300]",
*"time" => 2011-07-16T04:00:00.000Z,*
"@timestamp" => 2021-11-03T17:18:20.287Z,
"timelineid" => "14654039-c619-4bd5-8ac4-6988c31ad87e",
"id" => 71908,
"miscinfo" => nil,
"unitid" => "8813afed-2d28-4f14-ae3b-3e0c6bb3f780"
}