# Logstash-jdbc-input Oracle SQL issue

**URL:** <https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219>\
**Category:** Logstash\
**Created:** [April 12, 2017, 7:47pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219 "2017-04-12T19:47:12Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![s58smith](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@s58smith](https://discuss.elastic.co/u/s58smith)\
**Post date:** [April 12, 2017, 7:47pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219/1 "2017-04-12T19:47:12Z")

</div>

I'm having trouble trying to figure out why my SQL statement is not working. I've tested enough combinations that I have it narrowed down to a single line causing issue.  
"AND collection\_system\_date \>= SYSDATE - 300/86400"

But I can't see why the line is causing issue as I can use it in normal SQL client or other JDBC client just fine.

Below is the complete SQL statement I'm testing. If I comment out "--" the one line above it will run fine and return data. I've tried all combinations of paraphrases around the numbers and sysdate, but nothing works.

SELECT  
collection\_system\_date  
, ne\_name  
, collector\_name  
, collector\_rpu\_host\_name  
, collection\_status  
FROM  
collector\_statuses  
WHERE  
ne\_model like 'cyan%'  
AND collection\_system\_date \>= SYSDATE - 300/86400  
AND collection\_system\_date \<= SYSDATE

I've tried setting the sql\_log\_level to "debug" but then I get nothing at all in the /var/log/logstash/logstash-plain.log file. If it goes someplace else in debug mode I'm not finding it and could help knowing where.

Thanks

---

<div class="post-metadata">

**Author:** ![eperry](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eperry/32/551_2.png) [@eperry](https://discuss.elastic.co/u/eperry)\
**Post date:** [April 12, 2017, 10:54pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219/2 "2017-04-12T22:54:32Z")

</div>

does it work from SQLPLus?

well since SYSTEM date is the current time, and I presume I understand what your doing, You can't ever collect data in to the future unless your system time is not synchronized

just having collection\_system\_data \>= SYSDATE -300/86400 will give you all records from 5 minutes ago to now and in to the future which should never happen unless you have out of sync system clocks.

---

<div class="post-metadata">

**Author:** ![s58smith](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@s58smith](https://discuss.elastic.co/u/s58smith)\
**Post date:** [April 13, 2017, 3:11pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219/3 "2017-04-13T15:11:36Z")

</div>

There is no problem with my original SQL as I use it many other places. However....

Your reply got me to thinking that maybe something about the dates is not correct.  
I did a quick test with logstash to see what dates would return in a query of Oracle's builtin functions. I have noticed that everything is coming back in UTC time, but according to my query it should be in America/New\_York.

SELECT current\_date, current\_timestamp, dbtimezone, localtimestamp, sessiontimezone, sysdate, systimestamp FROM dual;

{"sysdate":"2017-04-13T14:43:00.000Z","@timestamp":"2017-04-13T14:43:00.462Z","current\_timestamp":"2017-04-13T14:43:00.427Z","@version":"1","sessiontimezone":"America/New\_York","localtimestamp":"2017-04-13T14:43:00.427Z"}

However even adjusting my query for the difference (subtract another 4 hours) doesn't return anything. I even went ahead and just passed it "- 1" to subtract whole date and still doesn't work.

I figured out turning on debug in logstash I could then also turn on debug in jdbc input filter. Below is the output from the log file.

* * *

```
[2017-04-13T11:06:59,195][DEBUG][logstash.agent] no configuration change for pipeline {:pipeline=>"main"}
[2017-04-13T11:07:00,359][DEBUG][logstash.inputs.jdbc] (0.143000s) SELECT * FROM (SELECT count(*) "COUNT" FROM (SELECT
to_char(collection_system_date,'MM/DD/YY HH24:MI:SS') collection_system_date
, ne_name
, collector_name
, collector_rpu_host_name
, collection_status
FROM
collector_statuses
WHERE
ne_model like 'cyan%'
AND collection_system_date >= sysdate - 1
AND collection_system_date <= sysdate
) "T1") "T1" WHERE (ROWNUM <= 1)
[2017-04-13T11:07:00,366][DEBUG][logstash.inputs.jdbc] Executing JDBC query {:statement=>"SELECT\nto_char(collection_system_date,'MM/DD/YY HH24:MI:SS') collection_system_date\n, ne_name\n, collector_name\n, collector_rpu_host_name\n, collection_status\nFROM\ncollector_statuses\nWHERE\nne_model like 'cyan%'\nAND collection_system_date >= sysdate - 1\nAND collection_system_date <= sysdate\n", :parameters=>{:sql_last_value=>1970-01-01 00:00:00 UTC}, :count=>0}
[2017-04-13T11:07:00,466][DEBUG][logstash.inputs.jdbc] (0.098000s) SELECT
to_char(collection_system_date,'MM/DD/YY HH24:MI:SS') collection_system_date
, ne_name
, collector_name
, collector_rpu_host_name
, collection_status
FROM
collector_statuses
WHERE
ne_model like 'cyan%'
AND collection_system_date >= sysdate - 1
AND collection_system_date <= sysdate

[2017-04-13T11:07:02,180][DEBUG][logstash.pipeline] Pushing flush onto pipeline
```

---

<div class="post-metadata">

**Author:** ![s58smith](https://avatars.discourse-cdn.com/v4/letter/s/4af34b/32.png) [@s58smith](https://discuss.elastic.co/u/s58smith)\
**Post date:** [April 13, 2017, 8:57pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219/4 "2017-04-13T20:57:36Z")

</div>

Closing topic as this was user error.

After further investigation I forgotten the test DB is always 2 days behind production. Once I moved the WHERE clause to compare back to 2 days plus it all worked.

---

<div class="post-metadata">

**Author:** ![eperry](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eperry/32/551_2.png) [@eperry](https://discuss.elastic.co/u/eperry)\
**Post date:** [April 14, 2017, 4:04pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219/5 "2017-04-14T16:04:27Z")

</div>

sometimes, it is good to just "voice" out the problem and that provides a solution as you talk it out.

Glad I could be a sounding board for you

---

<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:** [May 12, 2017, 4:05pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-oracle-sql-issue/82219/6 "2017-05-12T16:05:15Z")

</div>

Closed on May 12, 2017, 4:05pm UTC

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