Why does Logstash put the wrong time zone in ~/.logstash_jdbc_last_run?

Question

Logstash 5.2.1

The configuration below is Ok, the partial updates are woking. I just misunderstood the results and how time zone is used by Logstash.

jdbc_default_timezone Timezone conversion. SQL does not allow for timezone data in timestamp fields. This plugin will automatically convert your SQL timestamp fields to Logstash timestamps, in relative UTC time in ISO8601 format. Using this setting will manually assign a specified timezone offset, instead of using the timezone setting of the local machine. You must use a canonical timezone, Europe/Rome, for example.

---

I want to index some data from a PostgreSQL to Elasticseach with help of Logstash. The partial updates should be working.

But in my case, Logstash puts the wrong time zone in ~/.logstash_jdbc_last_run.

$cat ~/.logstash_jdbc_last_run 
--- 2017-03-08 09:29:00.259000000 Z

My PC/Server time:

$date
mer  8 mar 2017, 10.29.31, CET
$cat /etc/timezone 
Europe/Rome

My Logstash configuration.:

input {
  jdbc {
    # Postgres jdbc connection string to our database, mydb
    jdbc_connection_string => "jdbc:postgresql://localhost:5432/postgres"
    # The user we wish to execute our statement as
    jdbc_user => "logstash"
    jdbc_password => "logstashpass"
    # The path to our downloaded jdbc driver
    jdbc_driver_library => "/home/trex/Development/ship_to_elasticsearch/software/postgresql-42.0.0.jar"
    # The name of the driver class for Postgresql
    jdbc_driver_class => "org.postgresql.Driver"
    jdbc_default_timezone => "Europe/Rome"
    # our query
    statement => "SELECT * FROM contacts WHERE timestamp > :sql_last_value"
    # every 1 min
    schedule => "*/1 * * * *"
  }
}
output {
  stdout { codec => json_lines }
  elasticsearch {
    hosts => [ "localhost:9200" ]
    index => "database.%{+yyyy.MM.dd.HH}"
  }
}

Without jdbc_default_timezone the time zone is wrong too.

My PostgeSQL data:

postgres=# select * from "contacts";                                                                                               uid |         timestamp          |          email          | first_name | last_name
-----+----------------------------+-------------------------+------------+------------
   1 | 2017-03-07 18:09:25.358684 | [email protected]         | Jim        | Smith
   2 | 2017-03-07 18:09:25.3756   |                         | John       | Smith
   3 | 2017-03-07 18:09:25.384053 | [email protected]       | Carol      | Smith
   4 | 2017-03-07 18:09:25.869833 | [email protected]         | Sam        |
   5 | 2017-03-08 10:04:26.39423  | [email protected]        | T          | Rex

The DB data is imported like this:

INSERT INTO contacts(timestamp, email, first_name, last_name) VALUES(current_timestamp, '[email protected]', 'Sam', null);

Why does Logstash put the wrong time zone in ~/.logstash_jdbc_last_run? And how to fix it?

Answer 1

2017-03-08 09:29:00.259000000 Z mean UTC timezone, it's correct.