@Xiang Fu We are facing trouble when using the transformFunction: "DATETIMECONVERT(column_name, '1MILLISECONDSSIMPLE_DATE_FORMAT:yyyy-MM-dd HHmmss.SSS', '1MILLISECONDSEPOCH', '1:MILLISECONDS')" The column_name type is string and format is "1MILLISECONDSSIMPLE_DATE_FORMAT:yyyy-MM-dd HHmmss.SSS" This is working perfectly fine But when I am trying to do the same transformations to the another column whose format is "1MILLISECONDSSIMPLE_DATE_FORMAT:yyyy-MM-dd'T' HHmmss.SSSZ" and transformFunction: "DATETIMECONVERT(column_name, '1MILLISECONDSSIMPLE_DATE_FORMAT:yyyy-MM-dd\'T\'HHmmss.SSS\'Z\'', '1MILLISECONDSEPOCH', '1:MILLISECONDS')" Can you please help if the above timestamp format is supported in pinot transformation Function. In the doc I can't find any example for above - https://docs.pinot.apache.org/configuration-reference/functions/datetimeconvert

@Xiang Fu I am facing issue with the datetime column format '2024-03-12 075345+00:00'. I am not able to provide the proper format because of which in the pinot it is getting ingested in epoch and also the transformFunction is failing Caused by: java.lang.IllegalArgumentException: Invalid format: "2024-03-12 075345+00:00" is malformed at "+00:00"

You can try

select DATETIMECONVERT('2024-03-12 07:53:45+03:00', '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:sZ', '1:MILLISECONDS:EPOCH', '1:MILLISECONDS') from airlineStats limit 10

I tried to use the above format of yours in the below dateTimeFieldSpecs but I am not sure why it is fetching the epoch time "dateTimeFieldSpecs": [ { "name": "dateCreated", "dataType": "STRING", "format": "1MILLISECONDSSIMPLE_DATE_FORMAT:yyyy-MM-dd HHmmsZ", "granularity": "1:SECONDS" } ] The exception I am getting Caused by: java.lang.IllegalArgumentException: Invalid format: "1710201606000000" is malformed at "6000000" @Xiang Fu

oh, you data is in microsecond right

No my data is in the date format only I can read the parquet file which I am directly ingesting It is giving output for the column as dateCreated 0 2024-03-12 075345+00:00 1 2024-03-12 174750+00:00 2 2024-03-12 063012+00:00 3 2024-03-12 071740+00:00 4 2024-03-12 181005+00:00 But when I am running the pinot job it is converting into the epoch I read in the doc if don't provide the datetime format it default converts it into the epoch. But I am providing the format below - "dateTimeFieldSpecs": [ { "name": "dateCreated", "dataType": "STRING", "format": "1MILLISECONDSSIMPLE_DATE_FORMAT:yyyy-MM-dd HHmmsZ", "granularity": "1:SECONDS" } ]

I would suggest to define the column using

TIMESTAMP

as the dataType.

okay thank you will change the string to datetime datatype

timestamp

🙂