DateTime field within 24 hours from a specific hour

Question

it's a date time field (my_date_field__c)

I have to update the record if my_date_field__c is within the past 24 hours (> yesterday at 5pm and <= today at 5pm)

I was thinking to use a logic to add 5pm hours in YESTERDAY function to make it 5 pm from TODAY function

select id,my_date_field__c from employee__c where my_date_field__c = TODAY and HOUR_IN_DAY(my_date_field__c ) = 5

but it does not seems to be working and for testing I set the my_date_field__c value to be 5/28/2021 5:00 PM

Any idea how to achieve this soql/apex?

Answer 1

In Apex, just take away 24 hours:

Date last24Hours = DateTime.now().addDays(-1);
Employee__c[] employeesLast24Hours = [
  SELECT My_Date__c 
  FROM Employee__c 
  WHERE My_Date__c >= :last24Hours
];

If you really want the last 24 hours without a variable, it should look more like:

SELECT My_Date__c 
FROM Employee__c
WHERE My_Date__c = TODAY OR
 (My_Date__c = YESTERDAY AND HOUR_IN_DAY(My_Date__c) > 17)

Remember to use 24 hour format; PM hours are the hour plus 12 (5 + 12 = 17).