# Oracle JDBC connection - sql\_last\_value TIMESTAMP comparison isn't working as expected **URL:** \ **Category:** Logstash\ **Tags:** jdbc\ **Created:** [August 29, 2022, 7:25pm UTC](https://discuss.elastic.co/t/oracle-jdbc-connection-sql-last-value-timestamp-comparison-isnt-working-as-expected/313193 "2022-08-29T19:25:36Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![LChand](https://avatars.discourse-cdn.com/v4/letter/l/258eb7/32.png) [@LChand](https://discuss.elastic.co/u/LChand)\ **Post date:** [August 29, 2022, 7:25pm UTC](https://discuss.elastic.co/t/oracle-jdbc-connection-sql-last-value-timestamp-comparison-isnt-working-as-expected/313193/1 "2022-08-29T19:25:36Z")
Hello, Looking for some help asap please... I've an issue where same set of data i.e. all records for the current day gets picked with every scheduled run during the day even though the sql\_last\_value has the latest date-time value. It seems like the comparison isn't working. **Environment info -** \*Logstash server - logstash-7.10.2-1.x86\_64 ; \* _Logstash Server is in EDT time zone_ _JDBC Driver Library - ojdbc7.jar_ _Log\_Date Oracle field value is in UTC timezone_ **Here's an example :** Value in .logstash\_jdbc\_last\_run before the scheduled run at 12:00 pm EDT (Pls note the below value is in UTC coming from the Oracle view) _-- !ruby/object:DateTime '2022-08-29 12:44:09.000000000 Z'_ **Entry in Logstash logfile during the scheduled run ..** _[2022-08-29T12:30:03,549][INFO][logstash.inputs.jdbc] (2.489731s) select LOG\_DATE, ID, ACTUALVALUEDATE, PARTYID, CURRENCYCODE, ACTUALAMOUNT, CASHFLOWTYPEID, BANKACCTBANKID, BANKACCTNUMBER, CPTYBANKACCTNAME, CPTYBANKACCTNUMBER, STATE\_BEFORE, STATE\_AFTER, USERID from ppv where log\_date \> TIMESTAMP '2022-08-29 12:44:09.000000 +00:00' order by LOG\_DATE_ **I output the SQL records to a file and found all records for 29th including the ones prior to 2022-08-29 12:44:09 which should have been ignored if the comparison worked correctly.** I noticed that .logstash\_jdbc\_last\_run had a timestamp of 12:30 when the scheduled run took place -rw-r--r-- 1 logstash logstash 60 Aug 29 12:30 .logstash\_jdbc\_last\_run **and this the value .. there are actually no new records in the Oracle view after this date-time.** _--- !ruby/object:DateTime '2022-08-29 12:44:09.000000000 Z'_ **This is my input config :** input{ jdbc{ jdbc\_driver\_library =\> "/etc/logstash/sjdbc/ojdbc7.jar" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "jdbc:oracle:thin:@(DESCRIPTION\_LIST=(LOAD\_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(RETRY\_COUNT=3)(ADDRESS\_LIST=(LOAD\_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1521)))(CONNECT\_DATA=(SERVICE\_NAME=xxxxx)))(DESCRIPTION=(RETRY\_COUNT=3)))" jdbc\_user =\> "xxxxx" jdbc\_password =\> "xxxxx" schedule =\> "\*/55 \* \* \* \*" statement =\> "select LOG\_DATE, ID, ACTUALVALUEDATE, PARTYID, CURRENCYCODE, ACTUALAMOUNT, CASHFLOWTYPEID, BANKACCTBANKID, BANKACCTNUMBER, CPTYBANKACCTNAME, CPTYBANKACCTNUMBER, STATE\_BEFORE, STATE\_AFTER, USERID from ppv where log\_date \> :sql\_last\_value order by LOG\_DATE" record\_last\_run =\> true jdbc\_default\_timezone =\> "UTC" use\_column\_value =\> true tracking\_column =\> "log\_date" tracking\_column\_type =\> "timestamp" last\_run\_metadata\_path =\> "/xxxxx/.logstash\_jdbc\_last\_run" } } **I really appreciate any advice you can provide ... Thanks** ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [August 29, 2022, 7:38pm UTC](https://discuss.elastic.co/t/oracle-jdbc-connection-sql-last-value-timestamp-comparison-isnt-working-as-expected/313193/2 "2022-08-29T19:38:19Z")
this is what I will do "select LOG\_DATE, ID, ACTUALVALUEDATE, PARTYID, CURRENCYCODE, ACTUALAMOUNT, CASHFLOWTYPEID, BANKACCTBANKID, BANKACCTNUMBER, CPTYBANKACCTNAME, CPTYBANKACCTNUMBER, STATE\_BEFORE, STATE\_AFTER, USERID from ppv where log\_date \> sysdate - interval '60' minute order by LOG\_DATE" which will pull last one hour date. and you don't need last\_run, column\_value, tracking\_column etc... ---
**Author:** ![LChand](https://avatars.discourse-cdn.com/v4/letter/l/258eb7/32.png) [@LChand](https://discuss.elastic.co/u/LChand)\ **Post date:** [August 29, 2022, 7:48pm UTC](https://discuss.elastic.co/t/oracle-jdbc-connection-sql-last-value-timestamp-comparison-isnt-working-as-expected/313193/3 "2022-08-29T19:48:21Z")
Thanks for the response. But I would like to use the database field log\_date value (UTC timezone) to compare and pick from where it was left last rather than sysdate which is in EDT timezone. Strangely, I've jdbc connections to SQL servers and similar comparison with sql\_last\_value works fine there.. any ideas what might be the issue here? ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [August 30, 2022, 12:37pm UTC](https://discuss.elastic.co/t/oracle-jdbc-connection-sql-last-value-timestamp-comparison-isnt-working-as-expected/313193/4 "2022-08-30T12:37:54Z")
I try on early time when I was testing elk without 100% success. but decided to use my own control over logstash deciding what to pull ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 27, 2022, 12:38pm UTC](https://discuss.elastic.co/t/oracle-jdbc-connection-sql-last-value-timestamp-comparison-isnt-working-as-expected/313193/5 "2022-09-27T12:38:29Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.