xuhui-stripe opened a new issue, #8955:
URL: https://github.com/apache/pinot/issues/8955
We noticed issues recently that are related to `datetime_convert`:
----
Issue1:
Following query will fail when zoneid is specified as `EST `:
```
select
cast(
date_trunc(
'month',
datetime_convert(
datetime_convert(
date_timestamp ,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(UTC)',
'1:MILLISECONDS'
),
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(EST)',
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS'
) ,
'MILLISECONDS',
'EST'
) as timestamp
) as start_time,
lastwithtime("value", date_timestamp, 'LONG') as total,
'All' as group_name
from some_table
```
with error:
`java.lang.IllegalArgumentException: Timezone EST not supported`
But EST is clearly existed in jida time format provided in pinot
[doc](https://docs.pinot.apache.org/configuration-reference/functions/timezoneminute)
And if I change above timezone id to something else (like 'America/Toronto' or
'EST5EDT'), the query will work.
Could you let us know that what might caused it. (We found out this might be
related to java.time where three zone ids are commented out.)
----
Issue2:
Following query will fail when zoneid is specified as `Asia/Gaza` (in format
of SIMPLE_DATE_FORMAT) for datetime_convert functions:
```
select
cast(
date_trunc(
'day',
datetime_convert(
datetime_convert(
date_timestamp,
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(UTC)',
'1:MILLISECONDS'
),
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd tz(Asia/Gaza)',
'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS'
),
'MILLISECONDS',
'Asia/Gaza'
) as timestamp
) as start_time,
lastwithtime("value", date_timestamp, 'LONG') as total,
'All' as group_name
```
with error:
```
"errorCode": 200,
"message": "QueryExecutionError:\nProcessingException(errorCode:450,
message:InternalError:\njava.lang.NullPointerException\n\tat
org.apache.pinot.core.operator.combine.GroupByOrderByCombineOperator.mergeResults(GroupByOrderByCombineOperator.java:238)\n\tat
org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:120)\n\tat
org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:51)"
```
But `Asia/Gaza` is clearly existed in SIMPLE_DATE_FORMAT mentioned in pinot
[doc](https://docs.pinot.apache.org/configuration-reference/functions/datetimeconvert).
And if I change 'Asia/Gaza' to `Asia/Nicosia` the query will work. I also
found a list of timezone ids, both ids existed in the list:
https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
Could you let us know why queries are failing for some zone id keywords?
Appreciate the help! Thanks.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
