Help-Logic to check if the submitted date/time is after 2PM and skip the update of that record- Batch class

Question

This is the sample batch class that I am working on. This class is working fine but I need to add one more condition like, If status is set to submitted after 2PM, The number field should not be increased. But the batch job runs everyday at 3PM, So that record should be increased in next day run. How to achieve that? Any help would be appreciated. TIA.

global class BatchCounter implements Database.Batchable<sObject> {
 
    static final String businessHoursId;
   

    global Database.QueryLocator start(Database.BatchableContext bc) {
        String query = 'SELECT Id, numberField__c,statusField__c,Submitteddate__c FROM objectName__c WHERE Submitteddate__c !=null '+' AND statusField__c IN (\'Assigned\', \'Submitted\', \'InProgress\')';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext bc, List<TMA__c> tmas) {
        for (TMA__c tma : tmas) {
            if (isBusinessHours(businessHoursId)) {
               tma.numberField__c+=1;
            }
        }
        update tmas;
    }

    global void finish(Database.BatchableContext bc) {
    }

    private static Boolean isBusinessHours(Id businessHoursId) {
        BusinessHours businessHours = [SELECT Id FROM BusinessHours WHERE Id = :businessHoursId];
        if (businessHours != null) {
            Datetime currentDatetime = Datetime.now();
            return BusinessHours.isWithin(businessHoursId, currentDatetime);
        }
        return false;
    } }

I have modifed the execute method like below, To capture the record which is submitted after 2pm EST On Monday and exclude it in Monday's 3pm EST batch run.

global void execute(Database.BatchableContext bc, List<TMA__c> tmas)

{ Datetime today = Datetime.now().date();

Datetime cutoffEnd= Datetime.newInstance(today.year(),today.month(),today.day(),14,0,0);

for (TMA__c tma : tmas) {

if(tma.status__c =='Submitted" && tma.submitteddate__c>cutoffEnd)

{ continue; }

if (isBusinessHours(businessHoursId))

{ tma.numberField__c+=1;

}}

update tmas;

}

It excluded the 2 records as expected on Monday and updated all other records. But it should have updated these 2 records in yesterday's batch run , but it didn't. Batch updated all other records and excluded these 2 in yesterday's run which is not correct. What am I doing wrong? Please send some help here.

Answer 1

If you are saving the timestamp of the change in the status field into a custom field like Submitted_date__c, you can define the below query based on datetime :

global Database.QueryLocator start(Database.BatchableContext bc){
    Time todayTime = Time.newInstance(08, 30, 0, 0); 
    Date todayDate = System.today();
    String query = 'SELECT Id, Name FROM Account WHERE Submitted_Date_Time__c >='+ dateTimeFormat(todayDate.addDays(-1), todayTime) + ' AND Submitted_Date_Time__c<=' +  dateTimeFormat(todayDate, todayTime);
    System.debug(query);       
    return  Database.getQueryLocator(query);
}
private String dateTimeFormat(Date dateVal, Time timeVal){
    String month = dateVal.month() <= 9 ? '0' + dateVal.month() : dateVal.month() + '';
    String day = dateVal.day() <= 9 ? '0' + dateVal.day() : dateVal.day() + '';
    String hour = timeVal.hour() <= 9 ? '0' + timeVal.hour() : timeVal.hour() + '';
    String minute = timeVal.minute() < =9 ? '0' + timeVal.minute() : timeVal.minute() + '';
    String millisecond = timeVal.millisecond() <= 9 ? '0' + timeVal.millisecond() : timeVal.millisecond() + '';
    String val = dateVal.year() + '-' + month  + '-' + day + 'T' + hour + ':' + minute + ':' + millisecond + 'Z';
    //System.debug(val);
    return val;
}

Also, you can add your condition of status in the above query it should work.

Second way is if you enable history tracking and use History table to check last updated time of that particular field like below

    global void execute(Database.BatchableContext bc, List<Account> acc){
    Set<Id> accountIds = new Set<Id>();
    Set<Id> accountFilterId = new Set<Id>();
    Time todayTime = Time.newInstance(08, 30, 0, 0); 
    Date todayDate = System.today();
    DateTime fromDT = DateTime.newInstanceGmt(todayDate.addDays(-1), todayTime);
    DateTime toDT = DateTime.newInstanceGmt(todayDate, todayTime);
                
    for(Account tempAcc : acc){
        accountIds.add(tempAcc.Id);
    }
    
    for(AccountHistory accH : [SELECT AccountId FROM AccountHistory WHERE AccountId IN:accountIds AND Field='SDO_Certification_Level__pc' AND CreatedDate >=:fromDT AND CreatedDate <=:toDT ]){
         accountFilterId.add(accH.AccountId);
    }
    
    for(Account tempAcc : acc){
        if(accountFilterId.contains(tempAcc.Id)){
            // Do your processing
        }
    }
}

Let me know if you have any follow-up questions on the above or any improvement anyone might suggest.