how to compare time only from Datetime field in SOQL

Question

I have a custom Object which contains a Datetime field let say bookingDate It contains date and time of booking.

now i need to get all bookings starts from 2:00 PM to 4:00 PM regardless of date value. for current month.

I couldn't find any function/ query to get all rows which matches the criteria. Any ideas how to do that..? (If not then i will have to get all rows that comes under the month and filter data using logic. But that may not be efficient. as it will be fetching so many unnecessary records..!)

Answer 1

Check the list of date functions and date literals. Something like this?

SELECT Id, Name, SystemModstamp
FROM Account
WHERE SystemModStamp = THIS_MONTH 
    AND HOUR_IN_DAY(SystemModstamp) >= 14 
    AND HOUR_IN_DAY(SystemModstamp) <= 16

EDIT

Ravi pointed out that this will cover too much time (2 PM till 4:59:59 PM).

So there would be couple of ways to deal with it:

SELECT Id, Name, SystemModstamp
FROM Account
WHERE 
    (SystemModStamp = THIS_MONTH AND HOUR_IN_DAY(SystemModstamp) >= 14 AND HOUR_IN_DAY(SystemModstamp) <= 15)
    OR 
    SystemModStamp IN (2013-04-01T16:00:00Z, 2013-04-02T16:00:00Z, 2013-04-03T16:00:00Z, 
    2013-04-04T16:00:00Z, 2013-04-05T16:00:00Z, 2013-04-06T16:00:00Z, 2013-04-07T16:00:00Z)

Bit ugly but first part deals with 2:00 till 3:59 and then we ask about exact matches. You could really generate such long query or use a binding to List<DateTime> variable. Create one correct date & time and then keep calling addDays() on it for example.

Another way would be to accept it as is and then filter the times past 16:00:00 manually in the code? Not ideal but it'd still be better than fetching whole month's worth of bookings!

Answer 2

I am not sure if the "bookingDate" in your system is populated manually or via some logic. If it is done manually, then one way to do this would be to create a Number field on the object say "bookingTime(2,2)" so that the user could enter the time manually. Then later in your query you could do something like:

Double minTime = 2.00;

Double maxTime = 4.00;

List<YourObject> yourObjectRecords = [Select all the fields that you want from YourObject where bookingTime__c >=: minTime and bookingTime__c <=: maxTime]

If the "bookingDate" in your system is populated some logic you might have to create a trigger as suggested by @techTrekker.