Kibana Timezone settings

Elastic Stack Logstash
1

I am using logstash 5.1 streaming mysql data to elasticsearch 5.1, kibana 5 and this is my logstash code:

input {
  jdbc {
    jdbc_driver_library => "./mysql-connector-java-5.1.36.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://..."
    jdbc_user => "myuser"
    jdbc_password => "mypassword"
    jdbc_fetch_size => 200
    statement => " select * from mytable where datetime >= '2016-12-01 11:00:00' and datetime < '2016-12-01 12:00:00' limit 1"
  }
}
    filter {
          mutate {
                add_field => { "[type]"  =>  "log"}
         }
   }
output {
   stdout {codec => rubydebug}
  elasticsearch {
      hosts => ["xxx:9200"]
      index => "XXXXX"  # generate for unexpected messages
      template_name => "summary"
   }
}

As you noticed there is a datetime field in my mysql table, and I want the values to be the same in elasticsearch, but when I use logstash to stream the data to elasticsearch, from Kibana interface, I can see the data

The stdout {codec => rubydebug} result shows:

It means datetime field value is correct.

Also if I use sense to query this document, it shows correct value

However, if I use Kibana discover to see the doc, the value of datetime field is 5 hours ahead of the time, it also happens in Kibana discover histogram.

Pasted image1069×937 44.9 KB

How can I change the timezone settings of Kibana?

2

You're looking for the logstash date filter.

In the filter section of your config add this:

date {
  match => ["datetime", "ISO8601"]
}

PS: moving this over to the logstash room

3

However, if I use Kibana discover to see the doc, the value of datetime field is 5 hours ahead of the time, it also happens in Kibana discover histogram.

That's because Kibana by default adjusts the UTC timestamps in ES to the browser's local time. You can change this behavior via Kibana's advanced settings.

4

Thanks for your reply. The setting is already set to browser. And the problem still exists.

Because when I import from mysql using logstash, it recognises the datetime field as UTC, but my DB is using EST Timezone. So I need to tell logstash to acknowledge the field is EST. If not, there is time difference between my DB and elasticsearch.

The following is my logstash script:

input {
   stdin {}
  jdbc {
    jdbc_driver_library => "./mysql-connector-java-5.1.36.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://..."
    jdbc_user => "myuser"
    jdbc_password => "mypassword"
    jdbc_fetch_size => 200
    statement => " select * from mytable where datetime >= '2016-12-31 00:00:00' and datetime < '2016-12-31 01:00:00' limit 2"
  }
}

    filter {
      mutate {
            convert => [ "datetime", "string" ]
     }
        date {
                match => ["datetime", "ISO8601"]
                timezone => "America/Toronto"
                locale => "en"
                target => "@timestamp"
        }

          mutate {
               add_field => { "[type]"  =>  "log"}
              remove_field => ["datetime"]

         }
   }
output {
  stdout {codec => rubydebug}
}

This code does not set the time zone as "America/Toronto" as I thought, it is still recognized as UTC

5

I got it, I will convert the date field to UTC from mysql side, then it will solve the problem.

On Mysql side, the date is in ET time zone, I converted it to the UTC, then the problem is solved.
select CONVERT_TZ(datetime,'+00:00','+05:00') , field1, field2,... from mytable

6

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