Hi, It seems there's some compatibility issues between Pinot and Superset in regards to the time column: In Pinot, I have it defined like this

"dateTimeFieldSpecs": [
    {
      "name": "ts",
      "dataType": "STRING",
      "format": "1:SECONDS:SIMPLE_DATE_FORMAT:\"yyyy-MM-dd HH:mm:ss\"",
      "granularity": "1:MINUTES"
    }
  ]

In Superset, I must define the string format in the Python way for it to parse correctly:

%Y-%m-%d %H:%M:%S

When I try creating a chart, I get this error:

Apache Pinot Error
unsupported format character 'Y' (0x59) at index 58


This may be triggered by:
Issue 1002 - The database returned an unexpected error.

Because the query gets translated to this (note that if I remove the "DATETIMECONVERT" and simply use "ts" column it works fine):

SELECT DATETIMECONVERT(ts, '1:SECONDS:SIMPLE_DATE_FORMAT:%Y-%m-%d %H:%M:%S', '1:SECONDS:SIMPLE_DATE_FORMAT:%Y-%m-%d %H:%M:%S', '1:DAYS'),
       AVG(metric) AS "AVG_1"
FROM schema.table
WHERE ts >= '2021-02-01 00:00:00'
  AND ts < '2021-02-08 00:00:00'
GROUP BY DATETIMECONVERT(ts, '1:SECONDS:SIMPLE_DATE_FORMAT:%Y-%m-%d %H:%M:%S', '1:SECONDS:SIMPLE_DATE_FORMAT:%Y-%m-%d %H:%M:%S', '1:DAYS')
LIMIT 50000;

Has anyone encountered something similar? What is the solution?

hmm, I think the issue is that the date format in pinot query should use formt

yyyy-MM-dd HH:mm:ss

not

%Y-%m-%d %H:%M:%S

1. 0.999.0dev 2. This query works

SELECT ts,
       AVG(metric) AS "AVG_1"
FROM schema.table
WHERE ts >= '2021-02-01 00:00:00'
  AND ts < '2021-02-08 00:00:00'
GROUP BY ts
LIMIT 50000;

3. See filter in the query

thanks, also how do you define this ts column in superset? can you put a screen shot here

Here's the definition of the column in superset

ic, so you are building it from your own superset image

thanks

The fix is here: https://github.com/apache/superset/pull/13163