Logstash jdbc input plugin mongodb driver can not query time range

[john.zhang (ning john.zhang)]

Hi,
Please help . I want to search records in mongodb during 3 days.
this is my logstash conf:

input {
jdbc {
jdbc_driver_class => "com.dbschema.MongoJdbcDriver"
jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/mongojdbc3.1.jar"
jdbc_connection_string => "jdbc:mongodb://elk:123@192.168.1.245:27017/qisda"
jdbc_user => ""
jdbc_password => ""
schedule => "* * * * *"

statement => "db.dataLog.find({createDate:{ $lte: ISODate(:sql_last_value) ,$gte: new Date(ISODate(:sql_last_value).getTime()-3*24*60*60*1000)} }, {"appendInfo.cutMap":0,_id: 0})"

>  last_run_metadata_path => "/logstash/.logstash_jdbc_last_run3"
> }


error message:

[2021-04-12T12:38:00,721][ERROR][logstash.inputs.jdbc     ][main][d06cde4a8042fd780069608da1616ae2542e70a4a1b47c36f6e4f1b79816728f] Java::JavaSql::SQLException:
 Un-parsable date: 1614398400000 Configured formats: yyyy-MM-dd,dd-MM-yyyy,dd.MM.yyyy,d.MM.yyyy,dd/MM/yyyy,yyyy.MM.dd,M/d/yyyy: 
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")

I dont know how to resolve it , any advice!

[Badger]

That is not really a logstash question. In your SQL your are doing date manipulations. You are passing a timestamp which is milliseconds since the epoch, to something that expects one of yyyy-MM-dd, dd-MM-yyyy, dd.MM.yyyy, d.MM.yyyy, dd/MM/yyyy, yyyy.MM.dd, M/d/yyyy...

I do not know whether that something is MongoDB itself, or whatever is evaluating the expressions in SQL (not sure if that is done in the DB or the driver).