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

Will Briggs

12/18/2020, 7:15 PM
Essentially, I can’t figure out how to specify a time literal that is compatible with my timestamp column for doing math on it
m

Mayank

12/18/2020, 7:16 PM
As in, your app doesn't know the time unit?
It seems like a good feature ask. Should be easy to add, @Xiang Fu wdyt?
w

Will Briggs

12/18/2020, 7:17 PM
Let me rephrase - how can I subtract time from the value returned by NOW(), if it’s possible? It seems like a common use case.
x

Xiang Fu

12/18/2020, 7:17 PM
i think now() is supported as millisseonds epoch value
let me find an example query
w

Will Briggs

12/18/2020, 7:18 PM
thanks
m

Mayank

12/18/2020, 7:18 PM
No, the Interval
@Xiang Fu
w

Will Briggs

12/18/2020, 7:18 PM
I’m happy to directly modify NOW() as well, but it errors out on me for that, too
Copy code
SELECT COUNT(*) FROM myTable WHERE eventTimestamp >= (NOW() - 10000)
(e.g., give me everything in the last 10 seconds)
Copy code
[
  {
    "errorCode": 200,
    "message": "QueryExecutionError:\norg.apache.pinot.core.query.exception.BadQueryRequestException: Cannot convert value: '1.608319112661E12' to type: LONG\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:261)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.pruneRangePredicate(ColumnValueSegmentPruner.java:185)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.pruneSegment(ColumnValueSegmentPruner.java:105)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.prune(ColumnValueSegmentPruner.java:76)\n\tat
x

Xiang Fu

12/18/2020, 7:19 PM
hmmm
Copy code
select * from mytable where eventTimestamp > cast(now() - 10000 as long) limit 10
interval syntax is not yet supported
can you create an issue for this : @Will Briggs
m

Mayank

12/18/2020, 7:32 PM
@Amrish Lal fyi ^^
a

Amrish Lal

12/18/2020, 7:36 PM
@Will Briggs Please create a ticket with detailed description (and maybe a few examples) of the feature and I will look into implementing it.
m

Mayank

12/18/2020, 7:37 PM
@Amrish Lal in the query below, the request is to support INTERVAL;
Copy code
"SELECT dim1, dim2, DISTINCTCOUNTHLL(dim3) FROM myTable WHERE eventTimestamp >= NOW() - INTERVAL 10 MINUTES"
Also,
select * from mytable where eventTimestamp > cast(now() - 10000 as long) limit 10
requires cast, may be we can see if that can be fixed (assuming other SQL engines don't requrie)
x

Xiang Fu

12/18/2020, 7:39 PM
agreed
we should fix this
1
a

Amrish Lal

12/18/2020, 7:45 PM
ok, so seems like there is a bug fix here and along with that I will look into supporting ANSI INTERVAL syntax.
x

Xiang Fu

12/18/2020, 8:00 PM
great