topic Re: Formation duration as HH:mm in DQL

Formation duration as HH:mm

Jarren — Mon, 10 Feb 2025 10:31:29 GMT

I have a dashboard that lists task durations based on log timestamps. My DQL looks something like.


fetch logs
| parse content, "LD JSON:audit"
| fields timestamp, audit
| fieldsAdd uuid = audit[uuid], Task = audit[status_name], status = audit[status]
| fieldsRemove audit
| summarize {start_time = min (timestamp), 
end_time = max(timestamp)}
, by: {uuid, Task}
| fieldsAdd runtime = end_time - start_time
| fields Task, runtime, start_time, end_time

The field runtime has the datatype “duration”, is listed in nanoseconds and is formatted as hours. So in the dashboard the durations are shows as “3,15 h” and “11,99 h”. However I want to format it as HH:mm. So “3,15 h” is “03:09” and “2,99 h” is “11:59”. How can I do this?

Re: Formation duration as HH:mm

krzysztof_hoja — Mon, 03 Feb 2025 19:29:59 GMT

Unfortunately there is not with single function call, but with some arithmetic and string operations:

data record(d=duration(3.15, "h")), record(d=duration(2.99, "h")), record(d=duration(1.8, "m")), record(d=duration(120, "m")), record(d=duration(12.25, "h")), record(d=duration(5, "d")), record(d=duration(30, "s")), record(d=duration(29, "s")), record(d=duration(10, "ms")) | fieldsAdd h=toString(toLong(d/1h)), m=toString(toLong(round(d/1m)) % 60) | fields d, hm = concat( if(stringLength(h)<2, concat("0",h), else:h), ":", if(stringLength(m)<2, concat("0",m), else:m))

Re: Formation duration as HH:mm

Jarren — Tue, 04 Feb 2025 06:45:46 GMT

Thank you, it works!

Re: Formation duration as HH:mm

susmita_k — Thu, 07 Aug 2025 20:28:51 GMT

Hi, can you please tell me how did u achieve this with the runtime field u have in the dql ? I have similar query, but not able to change the format in my dashboard.