https://pinot.apache.org/ logo
p

Pedro Silva

05/31/2021, 1:09 PM
Hello, When defining a date time field from a string as:
Copy code
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:
Copy code
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]
m

Mayank

05/31/2021, 2:56 PM
This is because year expects a long and the data is stored in string format
p

Pedro Silva

05/31/2021, 2:58 PM
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
1
To use those transformations should I perform some transformation to the field first and then store the field as a long (ms since epoch) ?
m

Mayank

05/31/2021, 3:05 PM
Oh, in that case can you try date time convert with granularity as year, in the query?
p

Pedro Silva

05/31/2021, 3:07 PM
I don’t understand, can you clarify?
m

Mayank

05/31/2021, 3:08 PM
In query, instead of year use date time convert
p

Pedro Silva

05/31/2021, 3:49 PM
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?
I.e: Shown to the user in the format: yyyy-MM-dd on which they can do
year(field)
or
month(field)
Where a query like:
Select year(dateBirth), dateBirth from table where...
outputs:
1982 | 1982-02-13
m

Mayank

05/31/2021, 3:51 PM
Not that I am aware of. Seems like a good enhancement, want file an issue?
p

Pedro Silva

05/31/2021, 3:55 PM
Sure