How to group daily summary data by date

Hi I have been using below query to get average response time for application for duration of a day. Is it possible to group this daily summary by date and view it in dashboard??

SELECT average(duration) FROM Transaction WHERE appName IN (… app…names…) TIMESERIES 1 hour FACET appName LIMIT 20 SINCE yesterday UNTIL today

1 Like

Hey @rohankadu

You could open up your SINCE clause to see more data over time (perhaps SINCE 1 WEEK AGO) and set TIMESERIES 1 DAY rather than 1 Hour. That’ll group the data into one day chunks for your visualisation.

Alternatively, for a list view, you can try something like this:

SELECT average(duration) FROM Transaction WHERE appName IN (... app...names...) FACET appName, dateOf(timestamp) LIMIT MAX SINCE 1 week ago UNTIL today

That’ll look like this screenshot taken of our demo data:

1 Like

Hi @RyanVeitch thanks for the solution, it helped ! :slight_smile:
I can view the data in grouped by date format for applications.
Still need to check if the requirement is satisfied with the stake holder.

Thanks for the prompt solution.


1 Like

Hi @RyanVeitch is it possible to have all those date on X-axis and all the application names on y-axis for visualization in table format. It will be very easy to read that way.


Something like this.

Hey @rohankadu I think it’s possible to do this in the other direction…

That is, App Names on the X-Axis && Dates on the Y-Axis, see below;

SELECT filter(average(duration), where appName = 'AppName1') as 'AppName1', filter(average(duration), where appName = 'AppName2') as 'AppName2' FROM Transaction SINCE 4 DAYS AGO FACET dateOf(timestamp)

You can add more apps to this with extra Filter clauses:

filter(average(duration), where appName = 'AppName') as 'AppName'
1 Like

Hi @RyanVeitch the axis are opposite but it helps.

Indeed! As I mentioned I don’t think it’s possible to get the data exactly as you wish, but with axis flipped you can get close.

1 Like

You can use dates in filters if you use some tricks.

SELECT filter(average(duration), where dayOfMonthOf(timestamp) = '1') as '.1st' from Transaction facet appName since this month limit max