# How comparing an UTC datetime to a CET 'YYYY-mm-dd' date using jdbc\_static?

**URL:** <https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173>\
**Category:** Logstash\
**Created:** [May 25, 2020, 3:24pm UTC](https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173 "2020-05-25T15:24:59Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rom1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rom1/32/49699_2.png) [@Rom1](https://discuss.elastic.co/u/Rom1)\
**Post date:** [May 25, 2020, 3:24pm UTC](https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173/1 "2020-05-25T15:24:59Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [May 25, 2020, 6:10pm UTC](https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173/2 "2020-05-25T18:10:29Z")

</div>

> [@Rom1](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![Rom1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rom1/32/49699_2.png) [@Rom1](https://discuss.elastic.co/u/Rom1)\
**Post date:** [May 26, 2020, 1:34pm UTC](https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173/3 "2020-05-26T13:34:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [May 26, 2020, 2:58pm UTC](https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173/4 "2020-05-26T14:58:10Z")

</div>

Use mutate+gsub to remove the Z.

---

<div class="post-metadata">

**Author:** ![Rom1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rom1/32/49699_2.png) [@Rom1](https://discuss.elastic.co/u/Rom1)\
**Post date:** [May 27, 2020, 2:01pm UTC](https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173/5 "2020-05-27T14:01:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 24, 2020, 2:02pm UTC](https://discuss.elastic.co/t/how-comparing-an-utc-datetime-to-a-cet-yyyy-mm-dd-date-using-jdbc-static/234173/6 "2020-06-24T14:02:02Z")

</div>

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