[GitHub] [pinot] Jackie-Jiang commented on issue #7758: Problems to use transform functions and ingest dateTime columns with RFC3339 format

[GitBox <gi...@apache.org>]

Jackie-Jiang commented on issue #7758:
URL: https://github.com/apache/pinot/issues/7758#issuecomment-967703411


   The value you provided are of format `yyyy-MM-dd'T'HH:mm:ss.SSSZ`. Notice there is no single quotes around `Z`.
   
   In the `dateTimeFieldSpecs`, the format should be `1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd'T'HH:mm:ss.SSSZ`
   
   In the `dateTimeConvert` function, it should be `DATETIMECONVERT(operationDate, '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd''T''HH:mm:ss.SSSZ', '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM', '30:DAYS')`. Note that the literal should be single quoted, and single quote needs to be escaped with double single quotes per the sql syntax.
   
   I'd suggest doing ingestion transform to convert the time into millis since epoch and store it as `TIMESTAMP`. Read more about ingestion transform here: https://docs.pinot.apache.org/developers/advanced/ingestion-level-transformations


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org