Why is my timestamp in Logstash output appear different from value in Oracle column?

Elastic Stack Logstash
1

Hi,

I am using Logstash 5.6.3. I am fetching some rows from oracle db. In SQL Developer, my timestamp is displayed like 07-11-17 01:35:35.461000000 PM . When I stdout{ codec => json } in Logstash, it is displayed like 2017-11-07T08:05:35.461Z. Why is this happening and how can I fix this?

Thanks in advance.

2

What timezone is 07-11-17 01:35:35.461000000 PM?

3

hi @magnusbaeck its UTC

4

What does your configuration look like? What does an example event produced by Logstash look like? Use a stdout{ codec => rubydebug } output.

5

hi @magnusbaeck , below is the output

{
       "@version" => "1",
    "timefield" => 2017-11-07T08:05:35.461Z,
        "logtype" => "mylogs",
     "@timestamp" => 2017-11-09T05:46:41.158Z
}

here is my configuration;

input {
	jdbc {
		jdbc_driver_library => "/setup/GA563/logstash-5.6.3/ojdbc6.jar"
		jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
		jdbc_connection_string => "jdbc:oracle:thin:@192.168.0.1:1521:test"
		jdbc_user => "user1"
		jdbc_password => "user1"
		statement => "SELECT timefield FROM test_table where id = '1a'"
		add_field => { "logtype" => "mylogs" }
	}	
}
output{
	stdout{ codec => rubydebug }
}
6

Perhaps the timestamp is assumed to be local time and therefore converted to UTC. I don't think you can disable that conversion, but what if you change the query to convert the timestamp to local time? Then the UTC conversion should produce the expected results.

7

Hi @magnusbaeck,
can u kindly suggest a timezone to change my query? I can see that there is a difference of 6.30 hrs. How can I do this conversion? Kindly advise.

8

Sorry, I'm not fluent in Oracle's flavor of SQL.

9

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