Hi, I’m trying to build a query for data within 7 ...
# generals
Shawn Peng
02/23/2021, 1:09 AMHi, I’m trying to build a query for data within 7 days, but pinot is throwing error for
DATETRUNC('hour', second(now()), 'SECONDS')Shawn Peng
02/23/2021, 1:09 AMThis is the error message:
Shawn Peng
02/23/2021, 1:09 AM 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
},Shawn Peng
02/23/2021, 1:10 AMif 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?
Xiang Fu
02/23/2021, 2:08 AM
can you try
now()/1000Xiang Fu
02/23/2021, 2:09 AM
Xiang Fu
02/23/2021, 2:10 AM
or maybe
DATETRUNC('hour', now(), 'MILLISECONDS')s
Shawn Peng
02/23/2021, 3:19 AMI’ve tried both
DATETRUNC('hour', now(), 'MILLISECONDS')now()/1000s
Sidd
02/23/2021, 3:47 AMnow()/1000 should be cast as long @Shawn Peng
s
Shawn Peng
02/23/2021, 3:51 AMThanks @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