topic Re: Group by hours of the day with complete listing of hours in DQL

Group by hours of the day with complete listing of hours

KA — Mon, 17 Feb 2025 03:39:03 GMT

Hi Dynatrace Community,

Seeking some guidance on a DQL that presently fetches data for the past 28 days and groups by hours of the day which shows us the peak usage hours for our web application for a period of time. This works well however there are gaps in the hours of the day (no usage) which results from periods that has zero entries.

We want to ensure that all hours of the day (00-23) are included, even if they have zero entries. Below is the DQL and feel perhaps we should look utilise maketimeseries instead. #stilllearning

fetch spans, from: now() - 28d, to: now() - 1d | fieldsAdd hourOfDay = formatTimestamp(start_time, format: "HH") | summarize count = count(), by: { hourOfDay } | sort hourOfDay asc | fields hourOfDay, count

Re: Group by hours of the day with complete listing of hours

sinisa_zubic — Mon, 17 Feb 2025 12:54:00 GMT

Hi @KA

yes you should go the makeTimeseries way.

What do you think about this query

fetch spans, from: -28d | makeTimeseries { count = count(), start = start() }, interval:1h | fields count = record(count = count[], timestamp = start[]) | expand count | fields count = count[count], timestamp = formatTimestamp(count[timestamp], format: "HH") | summarize { count = sum(count)}, by: { timestamp } | sort timestamp asc

Best,
Sini

Re: Group by hours of the day with complete listing of hours

KA — Tue, 18 Feb 2025 00:24:00 GMT

Thanks @sinisa_zubic for your assistance. Have tested the DQL and the results include all hours of the day (00-23), including entries with zero logs - much appreciated and a great solution!

Of note, when presenting this in a Table visualization we see all hours of the day, however if we change the presentation to Categorical (bar layout), the zero entry periods are hidden - included a visual below for reference. To overcome this, we set the count default (0.01) with rounding.

Below is an alternative approach we found via a Community thread, however when presenting Categorical the x-axis label included the current date i.e. Feb 18 ... pref. your provided solution

| fieldsAdd timestamp = start_time - start_time@d + @d // group by hour + set default for zero entries | makeTimeseries count = count(default: 0), from: @d, to: @d+1d, interval: 1h