SQuirrel and JDBC driver SQL date format

[epical-mi]

Trying to select date fields from ES using JDBC driver and SQuirrel returns errors to the execution logs. All the date fields are seen as <Error> within the result set.

The select I'm trying to execute is: select * from kibana_sample_data_logs;

SQuirrel logs:

2019-04-01 16:03:22,016 [Thread-4] ERROR net.sourceforge.squirrel_sql.fw.sql.ResultSetReader - Error reading column data, column index = 9 java.sql.SQLDataException: Can not parse 2019-04-13 12:58:48.352Z as a Timestamp at com.amazon.opendistroforelasticsearch.jdbc.types.TypeHelper.stringConversionException(TypeHelper.java:33) at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.asTimestamp(TimestampType.java:85) at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.fromValue(TimestampType.java:46) at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.fromValue(TimestampType.java:29) at com.amazon.opendistroforelasticsearch.jdbc.types.BaseTypeConverter.convert(BaseTypeConverter.java:58) at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObjectX(ResultSetImpl.java:530) at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getTimestampX(ResultSetImpl.java:326) at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:315) at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp.readResultSet(DataTypeTimestamp.java:505) at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.readResultSet(CellComponentFactory.java:503) at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.doContentTabRead(ResultSetReader.java:613) at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:184) at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.createRow(ResultSetDataSet.java:242) at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet._setResultSet(ResultSetDataSet.java:208) at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setSqlExecutionTabResultSet(ResultSetDataSet.java:133) at net.sourceforge.squirrel_sql.client.session.mainpanel.SQLExecutionHandler.sqlResultSetAvailable(SQLExecutionHandler.java:431) at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processResultSet(SQLExecuterTask.java:540) at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processQuery(SQLExecuterTask.java:403) at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.run(SQLExecuterTask.java:209) at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82) at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.NumberFormatException: For input string: "352Z00000" at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.lang.Integer.parseInt(Integer.java:580) at java.lang.Integer.parseInt(Integer.java:615) at java.sql.Timestamp.valueOf(Timestamp.java:255) at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.asTimestamp(TimestampType.java:78) ... 19 more

[qreshi]

Thanks for reporting this issue. We're looking into it. Also, just to confirm, could you post a sample of the date fields from kibana_sample_data_logs?

[epical-mi]

Here is the sample of the date fields data.

{
  "took" : 7,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 14011,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "kibana_sample_data_logs",
        "_type" : "_doc",
        "_id" : "Zm-M2GkBesMuHbN9_-HK",
        "_score" : 1.0,
        "_source" : {
          "utc_time" : "2018-08-11T10:27:34.904Z",
          "timestamp" : "2019-04-13T10:27:34.904Z"
        }
      },
      {
        "_index" : "kibana_sample_data_logs",
        "_type" : "_doc",
        "_id" : "Z2-M2GkBesMuHbN9_-HK",
        "_score" : 1.0,
        "_source" : {
          "utc_time" : "2018-08-11T10:57:45.412Z",
          "timestamp" : "2019-04-13T10:57:45.412Z"
        }
      }
    ]
  }
}

[epical-mi]

Our own fix for this issue was this patch. Works now ok with SQuirrel and SQLWorkbenchJ

@@ -72,6 +72,7 @@ public class TimestampType implements TypeHelper<Timestamp> {
             // Make some effort to understand ISO format
             if (value.length() > 11 && value.charAt(10) == 'T') {
                 value = value.replace('T', ' ');
+                value = value.replace("Z", "");
             }

             if (calendar == null) {

[tbrugz]

I have a similar issue with timestamps in the following format:

2019-05-02T20:42:39.429+00:00

with a query like SELECT * FROM security-auditlog-2019.05.02

I may have a fix and will send a pull request