Hello When defining a date time field from a string as ```da

Question

Hello When defining a date time field from a string as ```dateTimeFieldSpecs name dateOfBirth dataType STRING format 1 DAYS SIMPLE DATE FORMAT yyyy MM dd T HH mm ss Z granularity 1 DAYS ``` Should you be able to apply <https docs pinot apache org users user guide query supported transformations datetime functions|datetime funcitons transformations> at query time For example retrieving the year of the field `select year dateOfBirth from ` I m getting parsing errors ```2021 05 31 12 52 50 536 ERROR BaseCombineOperator pqw 1 Caught exception while executing operator of index 0 query QueryContext tableName= HitExecutionView REALTIME selectExpressions= year dateOfBirth aliasList= null filter=null groupByExpressions=null havingFilter=null orderByExpressions=null limit=10 offset=0 queryOptions= responseFormat=sql groupByMode=sql timeoutMs=9994 debugOptions=null brokerRequest=BrokerRequest querySource QuerySource tableName HitExecutionView REALTIME pinotQuery PinotQuery dataSource DataSource tableName HitExecutionView REALTIME selectList Expression type FUNCTION functionCall Function operator YEAR operands Expression type IDENTIFIER identifier Identifier name dateOfBirth orderByList limit 10 queryOptions responseFormat=sql groupByMode=sql timeoutMs=9994 java lang NumberFormatException For input string 1997 02 06T00 00 00Z at java lang NumberFormatException forInputString NumberFormatException java 65 ~ 1 8 0 292 at java lang Long parseLong Long java 589 ~ 1 8 0 292 at java lang Long parseLong Long java 631 ~ 1 8 0 292 ```

Mayank · Answer

This is because year expects a long and the data is stored in string format

Pedro Silva · Answer

I thought the field was converted from the string representation during ingestion to an internal date time representation that could then be use any date time function

Pedro Silva · Answer

To use those transformations should I perform some transformation to the field first and then store the field as a long ms since epoch

Mayank · Answer

Oh in that case can you try date time convert with granularity as year in the query

Pedro Silva · Answer

I don t understand can you clarify

Mayank · Answer

In query instead of year use date time convert

Pedro Silva · Answer

Is there any way to specify a field as a date time field that is presented to the user as a human readeable string while still being able to use the date time transformations

Pedro Silva · Answer

I e Shown to the user in the format yyyy MM dd on which they can do `year field ` or `month field `

Pedro Silva · Answer

Where a query like `Select year dateBirth dateBirth from table where ` outputs `1982 | 1982 02 13`

Mayank · Answer

Not that I am aware of Seems like a good enhancement want file an issue

Pedro Silva · Answer

Sure

Pedro Silva · Answer