I am currently using Appian's query expression to group aggregated data by month for hundreds of thousands of records. We now need that capability to do the same aggregation and group by day and hour, but that is not possible with the query expression we're using. Does anyone have experience with this issue and/or insight into its most efficient solution?
Discussion posts and replies are publicly visible
Possibly add a View including HH and MM from datetime field, then aggregate on those?
SELECT *,
hour(`datetime`) hh, minute(`datetime`) mmFROM `table`
Yes, agree on completing this within the database if you have access to create/modify views. The SQL DATEPART() function is very handy for these scenarios, you can break out any part of the datetime value into separate columns to then be used in Appian aggregation, filtering, etc.
SELECT DATEPART(minute,dateTimeField) 'minute', DATEPART(hour,dateTimeField) 'hour', DATEPART(day,dateTimeField) 'day', DATEPART(week,dateTimeField) 'week', DATEPART(month,dateTimeField) 'month', DATEPART(year,dateTimeField) 'year' FROM YOUR_TABLE