# Logstash input jdbc plugin with informix database not working with timestamp

**URL:** <https://discuss.elastic.co/t/logstash-input-jdbc-plugin-with-informix-database-not-working-with-timestamp/62004>\
**Category:** Logstash\
**Created:** [October 2, 2016, 11:07am UTC](https://discuss.elastic.co/t/logstash-input-jdbc-plugin-with-informix-database-not-working-with-timestamp/62004 "2016-10-02T11:07:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![praskatti](https://avatars.discourse-cdn.com/v4/letter/p/ee59a6/32.png) [@praskatti](https://discuss.elastic.co/u/praskatti)\
**Post date:** [October 2, 2016, 11:07am UTC](https://discuss.elastic.co/t/logstash-input-jdbc-plugin-with-informix-database-not-working-with-timestamp/62004/1 "2016-10-02T11:07:01Z")

</div>

Hello,

I am using the JDBC input plugin to perform the sync between informix and Elasticsearch data store. I am using the "sql\_last\_value" criteria to distinguish old data and new data. I have a created\_date field in the table that I use.

# == create table movies\_new\_2 ( MovieID int, MovieName varchar(30), Country varchar(10), Year int, created\_date datetime YEAR TO FRACTION(5) not null DEFAULT DATETIME(0001-01-01 17:00:00.000) YEAR TO FRACTION(5) )

logstash jdbc config is as below:

==

> input {  
> jdbc {

> ```
> jdbc_driver_library => "/opt/jenzabar/jx/logstash-2.4.0/lib/ifxjdbc-3.70.JC5.jar"
> jdbc_driver_class => "com.informix.jdbc.IfxDriver"
> jdbc_connection_string => "jdbc:informix-sqli://abc.net:1234/test:informixserver=xyz"
> jdbc_user => "user"
> jdbc_password => "xxxxx"
> jdbc_fetch_size => 1000
> record_last_run => "true"
> statement => "select movieid, MovieName, Country, Year, created_date from movies_new_2 WHERE created_date > :sql_last_value"
> clean_run => "true"
> use_column_value => "true"
> tracking_column => "created_date"
> 
> ```

> ```
> }
> 
> ```
> 
> }

> output {

> ```
> stdout {
> codec => json_lines
> }
> 
> ```

> }

However, when I try to execute this I get the following error. note similar configuration worked fine with mysql database.

==  
Settings: Default pipeline workers: 8  
Pipeline main started  
Java::JavaSql::SQLException: Extra characters at the end of a datetime or interval.: select movieid, MovieName, Country, Year, created\_date from movies\_new\_2 WHERE convert\_tz(created\_date) \> '2016-09-30 06:54:35.655000' {:level=\>:error}  
Exception when executing JDBC query {:exception=\>#\<Sequel::DatabaseError: Java::JavaSql::SQLException: Extra characters at the end of a datetime or interval.\>, :level=\>:warn}  
Pipeline main has been shutdown  
stopping pipeline {:id=\>"main"}

==

subesquent I ran the config with ID as the sql\_last\_value. things started working fine. I switched back to date with clean\_run set to true. additionally physically removed ..logstash\_jdbc\_last\_run file. now I get a different error.

# == Java::JavaSql::SQLException: It is not possible to convert between the specified types.: select movieid, MovieName, Country, Year, created\_date from movies\_new\_2 WHERE created\_date \> 0 {:level=\>:error}

turns out sql\_last\_value is not initialized. what can I try to get past this?

regards  
Prasad

---

<div class="post-metadata">

**Author:** ![praskatti](https://avatars.discourse-cdn.com/v4/letter/p/ee59a6/32.png) [@praskatti](https://discuss.elastic.co/u/praskatti)\
**Post date:** [October 3, 2016, 6:09pm UTC](https://discuss.elastic.co/t/logstash-input-jdbc-plugin-with-informix-database-not-working-with-timestamp/62004/2 "2016-10-03T18:09:29Z")

</div>

appreciate any inputs here.

---

<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:** [July 6, 2017, 4:36am UTC](https://discuss.elastic.co/t/logstash-input-jdbc-plugin-with-informix-database-not-working-with-timestamp/62004/3 "2017-07-06T04:36:02Z")

</div>

Closed on July 6, 2017, 4:36am UTC