Grouping by date/time in Aggregate API

[cburkins]

I'd like to use the platform aggregate API (e.g. /api/now/stats/incident), using sysparm_count:true, and group the results by the "created_on" field, using sysparm_group_by:created_on.  The trick is that the created_on field is a glide_date_time field, and I'd like to choose my grouping (e.g. by hour, by day, by month, etc). 

Any idea how to do that ?

[Giles Lewis]

You would need to add custom fields to incident table: u_created_month, u_created_day, etc.

This is a reasonable solution if you only need to aggregate a couple of ways. But it will not work for arbitrary aggregations.

You can use calculated fields, which might save you the trouble of writing a before-insert business rule. But beware: you will probably find that aggregations on calculated fields do not work correctly for records inserted before the fields were added. Calculated fields are "calculated" when the record is viewed or saved.

[cburkins]

Looks like there's an API for Performance Analytics....   Does Performance Analytics help in any way here ?   I've never really used PA, but always had the impression that PA does time series data.    

[Giles Lewis]

I am not a Performance Analytics expert, but I think what Performance Analytics does is to collect and summarize the data in advance using whatever breakdown you choose. The summarized results are stored in separate PA tables. Suppose you are using PA, and collecting data by day and by month. In this case the day and month aggregates will be stored in the database. You do not need to use an aggregate API because the data is pre-aggregated. The PA API allows you to retrieve the pre-aggregated results.

One implication of Performance Analytics is that it is very fast because it does not need to do aggregation on the fly.

[Mike Patel]

you need to use sys_created_on instead of created_on

Ex: https://XXXXX.service-now.com/api/now/stats/incident?sysparm_query=active%3Dtrue&sysparm_count=true&sysparm_group_by=sys_created_on