Datetime format problem

[mrecodes]

I need to import the timestamp from a json file with the format of: YYYY-MM-DDThh:mm:ss.SSSZ for example: 2021-06-22T09:16:31.260Z. The characters T and Z makes problem while trying to visualize with superset. So I tried some transform functions such as: FromDateTime('timestamp', 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''') DATETIMECONVERT(timestamp, '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd''T''HH:mm:ss.SSS''Z', '1:SECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss', '1:SECONDS') And many more options, but no luck. What can be the problem here? The exception is as follows:

Error: {'errorCode': 150, 'message': 'PQLParsingError:\n' 'org.apache.pinot.sql.parsers.SqlCompilationException: Caught ' 'exception while parsing query: SELECT 'timestamp\n' 'FROM "mytable"\n' 'LIMIT 10000\n' '\tat ' 'org.apache.pinot.sql.parsers.CalciteSqlParser.compileCalciteSqlToPinotQuery(CalciteSqlParser.java:324)\n' '\tat ' 'org.apache.pinot.sql.parsers.CalciteSqlParser.compileToPinotQuery(CalciteSqlParser.java:108)\n' '\tat ' 'org.apache.pinot.sql.parsers.CalciteSqlCompiler.compileToBrokerRequest(CalciteSqlCompiler.java:35)\n' '\tat ' 'org.apache.pinot.core.requesthandler.PinotQueryParserFactory.parseSQLQuery(PinotQueryParserFactory.java:46)\n' '\tat ' 'org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleSQLRequest(BaseBrokerRequestHandler.java:212)\n' '\tat ' 'org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleRequest(BaseBrokerRequestHandler.java:194)'}

[xiangfu0]

I think you can set the time format in superset, please check this doc: https://docs.pinot.apache.org/integrations/superset#adding-a-derived-column

[mrecodes]

Yes exactly, I tried %Y-%m-%dT%H:%M:%S.%f and also %Y-%m-%d''T''%H:%M:%S.%f''Z but no luck. From that configuration Superset is trying to run the query that I mentioned in my previous message: FromDateTime('timestamp', 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''') DATETIMECONVERT(timestamp, '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd''T''HH:mm:ss.SSS''Z', '1:SECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss', '1:SECONDS') and I get the exception.

[xiangfu0]

for pinot queries, single quote is used for literals, double quote is used for identifiers. so 'timestamp ' should be "timestamp".

[xiangfu0]

For superset side, have you tried the time format like %Y-%m-%dT%H:%M:%S.%fZ or %Y-%m-%dT%H:%M:%SZ And what's the exceptions?