Problem with date filter

Hello,

I am trying to parse the following message (e.g):

2022-07-14T13:06:16

Using the dissect filter correctly:

dissect {
                mapping => {
                    "[message]" => "%{[my][date]}"
                }
            }

Wich gives me [my][date] = 2022-07-14T13:06:16. Well but when i try to use the date filter to parse this date with the correct timezone it doesn't seem to have any effect... The date filter i am using:

date {
                    match => ["[my[date]", "yyyy-MM-dd'T'HH:mm:ss"]
                    timezone => "Europe/Lisbon"
                    target => "[my][date]"
                }

When i go see the json structure in kibana i get the following:

"my" : {
  "date" :  "2022-07-14T12:06:16Z"
}

Which is not making any sense to me. I should be seeing the my.date field with 2022-07-14T13:06:16.

This is correct, dates in Elasticsearch are stored in UTC, so 2022-07-14T13:06:16 in Lisbon Time will be 2022-07-14T12:06:16Z in UTC.

Kibana will show you the date time based in the browser timezone, so it will always convert the UTC to your local time.

What could be an issue is if the time 2022-07-14T13:06:16 is not Lisbon time, if this is the reason, then you can't use Europe/Lisbon as the timezone in the date filter, you need to use the timezone of this date. If this is in UTC, just use UTC in the timezone.

hm, you are right.
I have modified my date filter to:

date {
  match => ["[my][date]", "yyyy-MM-dd'T'HH:mm:ss"]
  timezone => "UTC"
  target => "@timestamp"
}

And now on kibana i can see the correct value for the field [my][date] but unfortunately @timestamp still has UTC time... I want it to have UTC + 1

It is not possible, date fields in Elasticsearch are in UTC and you can't change that.

The only thing i could do would be changing the timezone on kibana correct?

If you have a date time that is not in UTC, you need to tell elasticsearch which timezone this date time is before ingesting, it will then convert it to UTC and store as UTC.

On Kibana, all date time fields will be converted to the timezone of the browser or any other time zone if you change it in the settings, but when you go to discover and look at the json document it will always show the date time in UTC, this cannont be changed.

What is the issue you are facing? Is the date still wrong in Discover on table view? The JSON document will always show the date time in UTC.

2 Likes

The issue is for example, i have this message that i want to parse:

x,y,2022-07-14T15:45:38

So, i did a dissect filter to map the values on the message to fields on logstash. The problem was that elasticsearch was storing this value with the wrong UTC time, but now with you helping i figured it out. Using the timezone "UTC" made elasticsearch store the correct time value.
Now, the problem is the timestamp, i would like to store it or try to convert it to UTC + 1.

Below is the date value that comes in the message (from the json file on kibana):
image

Below is the date value above but mapped on the [my][date] field (from the json file on kibana):
image

Now, the timestamp is on UTC and i dont know how to make it the same value as the [my][date] field. I have already tried to copy the value to @timestamp but seems not to be working.
image

In which timezone is that date time?

If the time 2022-07-14T15:53:06 is in UTC, then you need to set the timezone of the date filter to UTC.

If you have this information in the field my.date, then it will be something as what you already shared.

date {
  match => ["[my][date]", "yyyy-MM-dd'T'HH:mm:ss"]
  timezone => "UTC"
  target => "@timestamp"
}

This will parse the date into the @timestamp field.

You could also have a date filter after that with the my.date as target.

date {
  match => ["[my][date]", "yyyy-MM-dd'T'HH:mm:ss"]
  timezone => "UTC"
  target => "[my][date]"
}

But again, all dates in elasticsearch are in UTC, if you are using the my.date field as the source to the @timestamp field, they should not be different, since your fields are different, with an offset of one hour, you may be doing something else in the pipeline that you didn't share.

Can you share your entire pipeline so it makes clear what is happening?

So just to be sure... If the time of a log is in Europe/Lisbon, like for example 16:42:00 elasticsearch will store it as 15:42:00 UTC right? And on kibana since it is configured to use brower timezone it would show us UTC + 1 (since my browser would be located in Portugal) right?

Still, it confuses me a bit... I will store a date value that comes with the value of 16:42:00 and it is stored as 15:42:00...

The pipeline is huge, 600+ lines.

If the time in your log file is 16:42:00 and this time is in the Europe/Lisbon timezone, then it is UTC + 1.

If your date string does not have a timezone information like +01:00 in the end, then you need to tell Logstash when indexing that the time 16:42:00 is in a different timezone so it can be correctly converted to UTC.

You need something like this:

date {
  match => ["dateField", "yyyy-MM-dd'T'HH:mm:ss"]
  timezone => "Europe/Lisbon"
  target => "targetField"
}

Or:

date {
  match => ["dateField", "yyyy-MM-dd'T'HH:mm:ss"]
  timezone => "+0100"
  target => "targetField"
}

Logstash will then convert the time to the UTC time which will be 15:42:00 and Elasticsearch will correctly store it as 15:42:00 in UTC, which is the same as 16:42:00 in UTC + 1.

All date times in Elasticsearch will be in stored UTC, so if your date time string does not have any timezone information in it, both Logstash and Elasticsearch will assume that it is already in UTC and this can lead to confusion and wrong dates.

The issue is, if your date is not in UTC and does not have any timezone information, then you need to tell logstash which timezone this date field is when applying the date filter.

Kibana will always convert back from UTC to the browser timezone, but if you look in the json of the document it will always be in UTC since the data is stored in UTC.

1 Like

Leandro, thank you for your explanation because i was making a huge confusion about how this date filter works.

I though that if i had a date time value of for example 17:35:00 UTC + 1, i would need to see that exact value in the kibana Discovery JSON file. At the end of the day it is all about conversions... Like you said, storing 17:35:00 UTC + 1 is the same as storing it as 16:35:00 UTC.

Everything is now clear for me.
Thank you very much for your help.

In the JSON tab in Discover you will always see the value in UTC because it is the raw data stored in Elasticsearch, in the Table tab you will see the value converted to the browser timezone.

I agree that this is confusing sometimes, you just need to remember that every date field in elasticsearch will always be in UTC, the conversion is done in the visualization side, in this case, in Kibana Discover and Visualizations.

If for example you use a script to extract the data from Elasticsearch, you will get the raw value in UTC, not the converted one.

A workaround that some people use is to store an extra date field with the date without converting it, or maybe store a copy of the date field as a keyword field, just to help in visualizations.

1 Like

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