https://linen.dev logo
Join Slack
Powered by
How can I run aggregation queries using a timestam...
# general
j
How can I run aggregation queries using a timestamp column on 
sys
tables? I want to be able to run a query like:
Copy code
SELECT datasource, COUNT(*) AS tasks_count, MAX(created_time)
FROM sys.tasks
WHERE created_time >= '2024-11-04'
AND status = 'FAILED'
GROUP BY 1
I used to be able use this horrible hack:
Copy code
CAST(LEFT("start",10) || ' ' || RIGHT(LEFT("start",19),8) AS TIMESTAMP)
However, now I get errors when trying to use LEFT, RIGHT, SUBSTRING, etc... TIME_PARSE also produces errors as does CAST. Anyone have any tips?
a
TIME_PARSE also produces errors as does CAST.
Druid functions like 
TIME*
(the non-standard ones) don’t work on bindable tables (sys & info schema): https://druid.apache.org/docs/latest/querying/sql-metadata-tables/#system-schema
> However, now I get errors when trying to use LEFT, RIGHT, SUBSTRING, etc... As far as the other functions that used to work, was that on a Druid version older than 28.0.0? I wonder if the Calcite version upgrade with 28.0.0 changed something with how these functions interact bc these bindable tables just use Calcite’s interpreter
j
Yes it was working in a previous version of Druid. Every time we upgrade Druid I run into this issue and need to find a new way to parse the time columns.
a
Okay, gtk, if you figure out something that works on a newer Druid version, please let us know. I don’t think we have comprehensive test coverage for these Calcite functions, so we can perhaps add some simple test coverage to help future proof against Calcite upgrades.
And related GitHub issue where the proposed solution doesn’t seem to work anymore - https://github.com/apache/druid/issues/11660
j
Calcite supposedly has TO_TIMESTAMP, PARSE_TIME and PARSE_TIMESTAMP functions but they don't work in Druid
No match found for function signature PARSE_TIMESTAMP(<CHARACTER>, <CHARACTER>)
Ah, these are Dialect-specific Operators according to Calcite documentation 😢 Specifically, TO_TIMESTAMP compatibility is o, p, r (Oracle, Postgres, Redshift) and we are using Postgres as the backend. I wonder if I could add 
fun=postgresql
to the connection string....?
j
I've run into this as well, when trying to extract the shard type from the shard spec field ... I believe CASE statements and LIKE clauses still work ... but could be messy. Can you write the query with expression that you would like to use? Maybe we can come up with something.
j
I'd like to run a query like:
Copy code
SELECT datasource, COUNT(*) AS tasks_count, MAX(created_time) AS latest_failure
 FROM sys.tasks
 WHERE created_time >= '2024-11-04'
 AND status = 'FAILED'
 GROUP BY 1
Open in Slack
PreviousNext
Powered by