building a query that it returns the quantity of contacts modified in a 2 or more range of hours

Question

I'm trying to build a query where it returns the quantity of modified contacts between two specific hours today grouped by a hours range, for example, the number of contacts that were modified between 10 and 11 and between 11 to 12 grouped by that two ranges I builded a query like this one:

SELECT COUNT(Id) FROM Contact WHERE HOUR_IN_DAY(LastModifiedDate) > 10 AND HOUR_IN_DAY(LastModifiedDate) < 11 AND LastModifiedDate = TODAY

But I don't know how to build a query that it returns grouping more than one range of hours

I want to know if there is a way to do it at SOQL level, if so, how?

Answer 1

There are no "date literals" for periods of less than 24 hours, but you're on the right track. You could choose to use a query like this:

Set<Integer> hoursToFind = new Set<Integer>{ 9, 10, 12 };
Integer results = [SELECT Count() FROM Contact 
                   WHERE HOUR_IN_DAY(LastModifiedDate) IN :hoursToFind 
                   AND LastModifiedDate = TODAY];

This should find all contacts modified between 9:00 and 10:59:59 or 12:00 to 12:59:59.