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?

  • This query always returns zero results, because there is no integer more than 10 but less than 11. – sfdcfox Sep 4 '14 at 23:07

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.