# Problem with oracle dates though logstasg

**URL:** <https://discuss.elastic.co/t/problem-with-oracle-dates-though-logstasg/374770>\
**Category:** Logstash\
**Created:** [February 19, 2025, 1:47pm UTC](https://discuss.elastic.co/t/problem-with-oracle-dates-though-logstasg/374770 "2025-02-19T13:47:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Borja](https://avatars.discourse-cdn.com/v4/letter/b/ed8c4c/32.png) [@Borja](https://discuss.elastic.co/u/Borja)\
**Post date:** [February 19, 2025, 1:47pm UTC](https://discuss.elastic.co/t/problem-with-oracle-dates-though-logstasg/374770/1 "2025-02-19T13:47:47Z")

</div>

Hi all,

We are migrating some queries used on logstash on a previous DB2 pipeline to a new oracle database pipeline.

We have checked them on db clients like Dbvisualizer or dbviewer and running these converted queries on those clients they run ok and we get the results we expect to see...

But when we run exactly the same queries through logstash on the pipeline we get this error on 4 of them:

```auto
[2025-02-19T13:48:00,365][ERROR][logstash.inputs.jdbc][pasarelas][74271d402d8c348a7bb2902d60c97784c82a7b2ce1db74c13e541df17bc84b28] Java::JavaSql::SQLDataException: ORA-01830: date format picture ends before converting entire input string: select FEC_REGISTRO,
NOM_WS as SERVICIO,
IDE_USU_EMP,
HOR_INI_REGISTRO,
HOR_FIN_REGISTRO,
TO_DATE(CONCAT(CONCAT(TO_CHAR(CAST(FEC_REGISTRO as date),'YYYY-MM-DD'),' '),TO_CHAR(HOR_INI_REGISTRO,'TS')),'YYYY-MM-DD HH24:MI:SS') as FECHATMST,
NUM_INVOCACIONES,
NUM_EJE_CORREC,
NUM_EJE_ERROR,
NUM_NO_EJE_POOL,
NUM_TMP_TOT_ESP,
NUM_TMP_MED_ESP_P,
NUM_TMP_MAX_ESP_P,
NUM_TMP_MIN_ESP_P,
NUM_EJE_SIN_ESP,
NOM_NODO,
NUM_MAX_ESP_R_IN,
NUM_MED_ESP_R_IN,
NUM_TOT_ESP_R_IN
from DB2H.HCTBF1W where 
FEC_REGISTRO >= SYSDATE - interval '1' day and
TO_DATE(CONCAT(CONCAT(TO_CHAR(CAST(FEC_REGISTRO as date),'YYYY-MM-DD'),' '),TO_CHAR(HOR_INI_REGISTRO,'TS')),'YYYY-MM-DD HH24:MI:SS') >= CURRENT_TIMESTAMP - interval '120' minute and
substr(TO_CHAR(HOR_INI_REGISTRO,'TS'),1,2) = substr(TO_CHAR(sysdate - interval '60' minute,'TS'),1,2)

```

Any idea what we are missing??

best regards

Borja

---

<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:** [February 19, 2025, 2:01pm UTC](https://discuss.elastic.co/t/problem-with-oracle-dates-though-logstasg/374770/2 "2025-02-19T14:01:37Z")

</div>

I suspect those date manipulations. I wonder if one of the formats is not what you expect due to locale or langauge issues. Perhaps try examining exactly what the TO\_CHAR calls are returning in logstash. Can you do things like

```
SELECT TO_CHAR(HOR_INI_REGISTRO,'TS') from DB2H.HCTBF1W

```

etc.? And I mean do that in a jdbc input, not in a DB CLI.

---

<div class="post-metadata">

**Author:** ![Borja](https://avatars.discourse-cdn.com/v4/letter/b/ed8c4c/32.png) [@Borja](https://discuss.elastic.co/u/Borja)\
**Post date:** [February 20, 2025, 1:03pm UTC](https://discuss.elastic.co/t/problem-with-oracle-dates-though-logstasg/374770/3 "2025-02-20T13:03:48Z")

</div>

Hi Badger,

I was able to test what you mentioned and you were right. The TS format eliminates the zeros on the left of the time field and that caused it to fail.

I have changed the TS option to TO\_CHAR(HOR\_INI\_REGISTRO,'HH24:MI:SS') and logstash has ingested those queries ok.

Thanks!!

Borja