Query datetime within 24 hours at specific time and timezone

Question

How would I query the expiry date/time to a specific hour? I want the expired_Datetime__c to be exactly at 5:00pm,

say:

If "expired_datetime__c" field is within the past 24 hours (> yesterday at 5:00pm and <= today at 5:00pm) the timezone should in PST

I have this apex code: but it checks for exact 24 hours but could not able to figure out the specific time in pst.

DateTime last24Hours = DateTime.now().addDays(-1);
Employee__c emp = [SELECT Id, Name, Expired_DateTime__c Employee__c WHERE
                      Expired_DateTime__c >= :last24Hours];
system.debug('emp : ' + emp  );

Answer 1

DateTime values in the Salesforce database are always in GMT. That means to calculate the correct GMT time to use, you need to do some conversion from the desired time zone to GMT.

Time methods are a mixture of GMT and the user's local time zone, by default. To get to the appropriate time, you would normally want to use TimeZone methods by starting from GMT, then adjusting for the time zone offset.

For example, to figure out the GMT for PST (America/Los Angeles), you would:

DateTime now = DateTime.now();
DateTime endTime = DateTime.newInstanceGMT(
  now.yearGMT(), // We use GMT time, so also use GMT date.
  now.monthGMT(), 
  now.dayGMT(), 
  17, // 24 hour clock, from 0-23. 17 = 5pm.
  0, // Minutes
  0); // Seconds
TimeZone tz = TimeZone.getTimeZone('America/Los_Angeles');
DateTime endTimeInPST = endTime.addSeconds(-tz.getOffset(endTime)/1000);
DateTime startTimeInPST = endTimeInPST.addDays(-1);

Note that because we want to convert appropriately, we have to to use the negative offset (demonstrated above). When we do this, we will observe that 5PM in LA is midnight GMT the next day. We have to be careful to specify exactly what we want, and this means carefully paying attention to GMT and local time effects.

Of course, as per the last question you had, you'll want to use addDays(-1) to find yesterday's 5PM. If you debug these values, they would be (for today) a start time of 2021-05-30 00:00:00 and end time of 2021-06-01 00:00:00. When you put these into a SOQL query, you'll get the records that match the desired time.

It's also worth noting that since we're using GMT methods, the date itself may be off. You may need to check if it's tomorrow in GMT and adjust for this, but without knowing exactly when you run the statement, it's hard to tell conclusively.