[druid-user] Druid SQL Query top of sys.tasks

Hi Team,

I want to find last 2 hours failed task from “sys.tasks” with below query but getting error message

Query: select created_time from sys.tasks where created_time >= CURRENT_TIMESTAMP - interval ‘2’ Hour

Error: unknown exception For input string “01T20:40:53:736Z”
Java.lang.NumberFormatException
Thanks

Hey Pathik,

sys.tasks (or generally any sys table) is notorious and stores all the time and date fields as string, hence you will not be able to use time functions for those fields.

Thanks
Senthil
Sr. Solutions Architect, Imply

Hi ,

Any alertnative sql for last 2 hours failed tasks.

Thanks,
Pathik

OK so…!!!

I had a play around with CASTing and some string functions:

I couldn’t get the SUBSTRING function to work, so I used LEFT and RIGHT together instead to extract the timestamp. Note that this therefore does not take any timezones into account - so I’m guessing (???) that it will be in UTC.

Hopefully you can see where I was going, and therefore just add the WHERE clauses and then test it out.

SELECT
CAST (LEFT(created_time,10) || ' ' || RIGHT(LEFT(created_time,19),8) AS TIMESTAMP) AS __time,
task_id,
status
FROM sys.tasks
WHERE CAST (LEFT(created_time,10) || ' ' || RIGHT(LEFT(created_time,19),8) AS TIMESTAMP) > TIMESTAMPADD(HOUR, -2, CURRENT_TIMESTAMP)

Thanks.