https://pinot.apache.org/ logo
#general
Title
# general
s

Shawn Peng

02/23/2021, 1:09 AM
Hi, I’m trying to build a query for data within 7 days, but pinot is throwing error for
DATETRUNC('hour', second(now()), 'SECONDS')
, is this expected?
This is the error message:
Copy code
{
    "message": "QueryExecutionError:\norg.apache.pinot.core.query.exception.BadQueryRequestException: Caught exception while initializing transform function: datetrunc\n\tat org.apache.pinot.core.operator.transform.function.TransformFunctionFactory.get(TransformFunctionFactory.java:206)\n\tat org.apache.pinot.core.operator.transform.function.TransformFunctionFactory.get(TransformFunctionFactory.java:201)\n\tat org.apache.pinot.core.operator.transform.function.TransformFunctionFactory.get(TransformFunctionFactory.java:201)\n\tat org.apache.pinot.core.operator.filter.ExpressionFilterOperator.<init>(ExpressionFilterOperator.java:56)\n\tat org.apache.pinot.core.plan.FilterPlanNode.constructPhysicalOperator(FilterPlanNode.java:125)\n\tat org.apache.pinot.core.plan.FilterPlanNode.run(FilterPlanNode.java:69)\n\tat org.apache.pinot.core.plan.DocIdSetPlanNode.run(DocIdSetPlanNode.java:41)\n\tat org.apache.pinot.core.plan.ProjectionPlanNode.run(ProjectionPlanNode.java:52)\n\tat org.apache.pinot.core.plan.TransformPlanNode.run(TransformPlanNode.java:52)\n\tat org.apache.pinot.core.plan.SelectionPlanNode.run(SelectionPlanNode.java:83)\n\tat org.apache.pinot.core.plan.CombinePlanNode$1.callJob(CombinePlanNode.java:127)\n\tat org.apache.pinot.core.plan.CombinePlanNode$1.callJob(CombinePlanNode.java:113)\n\tat org.apache.pinot.core.util.trace.TraceCallable.call(TraceCallable.java:44)\n\tat java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)",
    "errorCode": 200
  },
if i replace now() with a timestamp column, the same query works
x

Xiang Fu

02/23/2021, 2:07 AM
i think now() give the milliseconds?
can you try
now()/1000
or maybe
DATETRUNC('hour', now(), 'MILLISECONDS')
?
s

Shawn Peng

02/23/2021, 3:19 AM
I’ve tried both
DATETRUNC('hour', now(), 'MILLISECONDS')
and
now()/1000
, same error
s

Sidd

02/23/2021, 3:47 AM
now()/1000 should be cast as long @Shawn Peng
s

Shawn Peng

02/23/2021, 3:51 AM
Thanks @Sidd for the help! this where clause works:
DATETRUNC('hour', created_at_seconds, 'SECONDS') >= DATETRUNC('hour', cast(now()/1000 as long), 'SECONDS')
👍 2
x

Xiang Fu

02/23/2021, 4:16 AM
btw, the above query should be same as below ?
Copy code
DATETRUNC('hour', created_at_seconds, 'SECONDS') >= cast(now()/3600000 as long) * 3600