Add ability to return standardized datetime

[lyndonbauto]

It would be nice to have the ability to return a standardized datetime format from the SQL plugin. This will reduce the requirement of drivers using the plugin to support all the different formats (there is quite a few).

We will further investigate this feature on our end.

[abbashus]

Reference:
https://www.elastic.co/guide/en/elasticsearch/reference/7.4/date.html
https://www.elastic.co/guide/en/elasticsearch/reference/7.4/mapping-date-format.html

Current behaviour:

Default formats are strict_date_optional_time||epoch_millis

PUT /my_index?pretty
{
  "mappings": {
    "properties": {
      "date": {
        "type":   "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      }
    }
  }
}

PUT /my_index/_doc/1?pretty
{ "date": "2015-01-01" }
 
PUT /my_index/_doc/2?pretty
{ "date": "2015-01-01 12:10:30" }

PUT /my_index/_doc/3?pretty
{ "date": 1420070400001 }

POST _opendistro/_sql/?
{
  "query" : """
  SELECT date FROM my_index LIMIT 5
  """
}
 
{
  "schema": [{
    "name": "date",
    "type": "date"
  }],
  "total": 3,
  "datarows": [
    ["2015-01-01"],  <--- format_1
    ["2015-01-01 12:10:30"], <--- format_2
    [1420070400001] <--- format_3
  ],
  "size": 3,
  "status": 200
}

As a part of JDBC/JSON result, we get date in the string format supplied at the time of indexing, though internally they are stored as a long number representing milliseconds-since-the-epoch.

The plan is to return the date response in a single format.

[abbashus]

While testing for datetime type via Tableau, got the following error.

Error from Tableau jdbcserver.log

2020-01-16 16:11:09.939 -0800 (,,,,3) grpc-default-executor-0 : INFO  com.tableau.connect.service.JDBCService - Running query
   SELECT 1 AS `Number of Records`,
     `my_index`.`date` AS `date`
   FROM `my_index`
   LIMIT 1000
2020-01-16 16:11:09.991 -0800 (,,,,3) pool-3-thread-1 : ERROR com.tableau.connect.service.QueryTask - Query for protocol 3 exiting with error class java.sql.SQLDataException
2020-01-16 16:11:09.991 -0800 (,,,,3) grpc-default-executor-0 : ERROR com.tableau.connect.grpc.JDBCGrpcService - Failed in fetchResults. SQLState=null, ErrorCode=0.
java.sql.SQLDataException: Can not parse 2015-01-01 as a Timestamp
	at com.amazon.opendistroforelasticsearch.jdbc.types.TypeHelper.stringConversionException(TypeHelper.java:33) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.asTimestamp(TimestampType.java:99) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.fromValue(TimestampType.java:47) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.fromValue(TimestampType.java:30) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.amazon.opendistroforelasticsearch.jdbc.types.BaseTypeConverter.convert(BaseTypeConverter.java:58) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObjectX(ResultSetImpl.java:530) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getTimestampX(ResultSetImpl.java:326) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:1037) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]
	at com.tableausoftware.data.ProtobufResultSet.addValue(ProtobufResultSet.java:316) ~[jdbcserver.jar:1.4.1]
	at com.tableausoftware.data.ProtobufResultSet.addRow(ProtobufResultSet.java:250) ~[jdbcserver.jar:1.4.1]
	at com.tableausoftware.data.ProtobufResultSet.fetchRows(ProtobufResultSet.java:177) ~[jdbcserver.jar:1.4.1]
	at com.tableausoftware.data.ProtobufResultSet.nextBlock(ProtobufResultSet.java:189) ~[jdbcserver.jar:1.4.1]
	at com.tableau.connect.service.QueryTask.readData(QueryTask.java:146) ~[jdbcserver.jar:1.4.1]
	at com.tableau.connect.service.QueryTask.call(QueryTask.java:101) ~[jdbcserver.jar:1.4.1]
	at com.tableau.connect.service.QueryTask.call(QueryTask.java:56) ~[jdbcserver.jar:1.4.1]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_231]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_231]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_231]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_231]
Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
	at java.sql.Timestamp.valueOf(Timestamp.java:204) ~[?:1.8.0_231]
	at com.amazon.opendistroforelasticsearch.jdbc.types.TimestampType.asTimestamp(TimestampType.java:94) ~[opendistro-sql-jdbc-1.3.0.0.jar:?]

Data used:

PUT /my_index?pretty
{
  "mappings": {
    "properties": {
      "date": {
        "type":   "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
      }
    }
  }
}

PUT /my_index/_doc/1?pretty
{ "date": "2015-01-01" }

PUT /my_index/_doc/2?pretty
{ "date": "2015-01-01 12:10:30" }

PUT /my_index/_doc/3?pretty
{ "date": 1420070400001 }

Query shot from tableau:
  SELECT 1 AS `Number of Records`,
               `my_index`.`date` AS `date`
   FROM `my_index`
   LIMIT 1000

POST _opendistro/_sql/?
{
  "query" : """
  SELECT date FROM my_index
  """
}

{
  "schema": [{
    "name": "date",
    "type": "date"
  }],
  "total": 3,
  "datarows": [
    ["2015-01-01"],
    ["2015-01-01 12:10:30"],
    [1420070400001]
  ],
  "size": 3,
  "status": 200
}

For OpenDistro SQL JDBC driver, ES date type is being mapped to JDBC.Timestamp which expects the format yyyy-mm-dd hh:mm:ss[.fffffffff]. This should be the format to be adopted by SQL plugin for date type. The value of the date-time will be UTC (as internally stored by elasticsearch), and it should be up to the consuming application to convert to appropriate datet-time object based on timezone supplied.