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 ?

[cburkins]

Mike, good reply, and technically correct, that will work.   But it only gathers together incidents that were created on the exact same second.  We're pretty big, and that occasionally does happen, but what I'm really looking for is to understand how many incidents were created per hour.  

[Giles Lewis]

You know, if you want to see the number of incidents created per hour on any particular day, you can create a trend chart in ServiceNow. Just select

• Type: Trend
• Trend by: Created
• Per: Hour
• Aggregation: Count
• No. Groups: Show all.

This will also work if you want to see the number of incidents created each hour of the day, and you do not care which day.

You can also trend per Date.

The fact that you are trying to use an API suggests that you are pulling the data into some other tool for analysis or display. I am not sure what that other tool might be; but you may want to consider simply pulling the raw data, and letting the other tool do the aggregation. 

[cburkins]

@gflewis I think you're reasoning is sound; however, I'm constructing a live dashboard, and would like to pull this data every few minutes (maybe even every 60 seconds).  Our incident volumes are pretty high, so pull the raw data and then performing the calculations myself is certainly one option, though perhaps a bit unkind to the backend database server (which I own).   So, would prefer the backend API to do the calculation for me, and spit back the desired counts.   Looks like I'm out of luck (for now)