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 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]

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

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

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

I don’t understand, can you clarify?

In query, instead of year use date time convert

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?

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

Sure