How comparing an UTC datetime to a CET 'YYYY-mm-dd' date using jdbc_static?

Hi all,
I'm trying to explain my problem here and hope you'll provide a solution to me...

I've a jdbc input that reads occurrences in a database, especially a datetime field in UTC:
evtUtcDate

I need to enrich the event using a jdbc_static filter to get the data regarding this datetime:

jdbc_static {
loaders => [ {
id => "refdata"
query => "SELECT * FROM XXXX"
local_table => "refdata"
} ]
local_db_objects => [ {
name => "refdata"
index_columns => ["evtStart","evtEnd"]
columns => [ ["evtStart", "date"], ["evtEnd", "date"], ["info", "varchar(200)"] ]
} ]
local_lookups => [ {
query => "SELECT info FROM refdata WHERE evtStart <= :evt_date AND evtEnd >=:evt_date"
parameters => {
evt_id => "[id]"
evt_date => "[evtUtcDate]"
}
target => "tmp_ref"
} ]
}

My problem is that in the referential database, the format of evtStart and evtEnd is "YYYY-mm-dd" in CET timezone.

So when I compare it to an UTC date, the result could be wrong and makes logstash retrieving and enriching the event with the wrong information.

Ex:
Evt:
evtUtcDate= 2020-05-24 23:30:00.000Z

Reference:
evtStart=2020-05-15 evtEnd=2020-05-24 info=TEST1
evtStart=2020-05-25 evtEnd=2020-05-30 info=TEST2

Event is enriched with TEST1 instead of TEST2

The only way I imagine is to convert one of the date (evtUtcDate) to a new one with the CET timezone and format it as "%Y-%m-%d"

  • Is there any other solution?
  • How can I do that with Logstash?

Use a date filter. If you have a date that is UTC and want to move it to CET (Etc/GMT-1) then I think you will have to set the timezone option on the date filter to Etc/GMT+1.

Hello Badger,

Your solution won't work.
When setting the timezone parameter on the date filter, it indicates to Logstash what is the timezone of the date field in order to convert it to UTC in the target field.

Here the evtUtcDate is formated with a 'Z' at the end. So Logstash assumes that it is an UTC date and ignores the timezone option.

Use mutate+gsub to remove the Z.

Hi,

I've found a ruby solution:

ruby {
code => "event.set('[@metadata][evtCESTDate]',event.get('[evt][dateUtc]').time.localtime.strftime('%Y-%m-%d'));"
}

'localtime' use the server timezone to modify the timezone of the date.
the field evtCESTDate in metadata is correctly formated and I can use it in the comparison

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