Druid SQL Query top of sys.tasks

[pathik poddar]

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 

[Senthil Valli]

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 

[pathik poddar]

Hi ,

Any alertnative sql for last 2 hours failed tasks.

Thanks,

Pathik 

--
You received this message because you are subscribed to the Google Groups "Druid User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/07b9d929-547f-4dff-b5c6-72ebc68bdd3cn%40googlegroups.com.

[Peter Marshall]

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)

[pathik poddar]

Thanks.

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/2ccf7fc1-769e-4b56-bb3a-aec36363daean%40googlegroups.com.