Date times SQL and output differents values

[Mbt_Mbt]

Hi,

Good afternoon,

I am having problems viewing data of type date in kibana.

I am using an elk platform with the jdbc connector. Where to make the query I get date type information.

In the database I have the field loaded filled with 2020-02-02 17: 52: 48.8832020-02-02 17: 52: 48.883 the output I get has the value: loaded: 2020-02-02T16: 52: 48.883Z

I have tried unsuccessfully to change the format in different ways.

How could I do so that data output is exactly the content stored in the database?

[Mbt_Mbt]

Tried another possibility

filter {
date {
locale => "en"
match => ["loaded", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"]
timezone => "Europe/Vienna"
target => "@timestamp"
}
}

but doesnt work, any idea?

[Badger]

I am not sure what your question is, but ...

elasticsearch saves timestamps as the number of milliseconds since the epoch, always in UTC. If I remember correctly elasticsearch will respond to queries with dates like 2020-02-02T16: 52: 48.883Z (where the Z indicates it is UTC).

kibana has a configurable date format and translates the date into the browsers local timezone by default.

I am not sure, but I think a jdbc input reading Date columns will assume them to be in the local timezone of the logstash server. logstash will convert the timestamp to UTC. If the DB and logstash are in different timezones you can control the timezone conversion on the connection string for some drivers (this drives the TZ that is input to logstash, not its output, which is always UTC).

Can you provide more detail on what your issue is and what your configuration is?

[Mbt_Mbt]

@Badger

elasticsearch saves tim

Hi Badger,

Thanks for answering.

The situation is the following.

We have a database from which we want to extract data for later viewing in Kibana.

To do this we apply an ELK platform through the JDBC connector, we consult the database.

Date data is displayed differently from that stored in the database.

We need the data to be displayed in the same way that they are in the database are to apply any changes.

In the database we have 2020-02-03 07: 32: 58.373 but when we visualize the data the value we have stored is 2020-02-03T06: 32: 58.373Z

I need the data to be displayed in the same way that they are represented in the bats without applying any changes.

Thank you very much for your help

[Mbt_Mbt]

tried including in config file

jdbc_default_timezone => "Europe/Paris"

But still showing the same date times

[Badger]

It sounds like the problem is that the jdbc input is converting the date to a timestamp, and you want it kept as a string. Try adding a CAST in the SELECT to force it to be a string.

[Mbt_Mbt]

thank you very much for your help. But I need to keep the parameter as tiemstam to filter in range later.

Any idea how to avoid changing my time?
It is exactly one hour less than what I expect to receive in all cases.

Thanks in advance

[Mbt_Mbt]

filter{
date {
match => [ "loaded", "yyyy-MM-dd HH:mm:ss.SSS"]
timezone => "Europe/Paris"
}
}

doesnt work, im not getting the solution

[Mbt_Mbt]

filter{
date {
match => [ "loaded", "MMM dd yyyy HH:mm:ss",
"MMM d yyyy HH:mm:ss", "ISO8601","yyyy-dd-MM HH:mm:ss.SSS" ]
}
}

Gives me _dateparsefailure

The input is 2020-02-03 16:32:04.927 so the yyyy-dd-MM HH:mm:ss.SSS should be ok? Is right?

[Mbt_Mbt]

the mapping:

"loaded": {
"type": "date"
},

i need to change the timezone because logstash by default assigns UTC and I need it to be represented as local time zone

any idea how could i do it please?

Thanks

[Mbt_Mbt]

tried with code but still getting wron results...

filter{
ruby {
code => "
t = event.get('loaded').time
t.localtime('+01:00')
event.set('logdatetime', t.strftime('%Y-%m-%dT%H:%M:%S +0100'))
"
}
}

What im doing wrong?

Thank you very much

[elasticforme (Sachin)]

I have posted about exact same case many time over and not a pretty good answer from anyone.

This is what I did. and working fine. I am in America/Chicago timezone.

Here is example. with submitted (date field)

filter {

   mutate{
        add_field => { "submitted_timezone" => "%{submitted}" }
   }

date {
      match => ["submitted", "dd-MMM-yy HH:mm", "ISO8601"]
      target => "submitted"
   }

   date { match => ["submitted_timezone", "dd-MMM-yy HH:mm", "ISO8601"]
        timezone => "Etc/UTC"
        target => "submitted_timezone"
   }
}

Here what I did is that, submitted is automatically handle by ELK. that means it is converted from current time to utc (added 6hour)

Then on kibana side it will be handled correctly via all visulization,
Make sure to setup timezone on kibana to America/Chicago

Now when I run a sql query (either via REST api or dev tool ) I use field submitted_timezone which is display as exact same time as I received from source.

I hope this make sense. I have spend countless hour to make sure it works.

If you just do last part and assign timezone your date will stay as is. But then it will show up 6+ hour on kibana (now time)