Get the actual Previous Fire Time of a CronTrigger / Scheduled Apex

Question

I am exeuting Scheduled Apex that should do processing based on the last execution time of the particular code. I query some records for processing and I only want to query those records, that have been changed (SystemModStamp) since the last time the code executed. So I am looking for something lile this:

SELECT ... FROM sObject WHERE SystemModStamp >= :LastExecution

Where LastExecution is automatically calculated during the exceution. If I have code that does not execute within the Cron (say, in Anonymous Apex), this is relatively easy to accomplish:

DateTime LastExecution = DateTime.valueOf([SELECT MAX(PreviousFireTime)LastRun FROM CronTrigger WHERE CronJobDetail.Name LIKE :cronName][0].get('LastRun')

or even easier, if I know the CronTrigger Id:

DateTime LastExecution = [SELECT PreviousFireTime FROM CronTrigger WHERE Id = :arbitraryId].PreviousFireTime

Unfortunately, this pattern does not work if the latter unit is executed from within the scheduled context (thus, retrieving the Trigger id from SchedulableContext.getTriggerId()). Salesforce updates the CronTrigger record before the code is executed. PreviousFireTime is always the time of execution.

I also tried quering the AsyncApexJob, but for standard recurring CronTriggers that are scheduled once, there is only one corresponding record in the queue that never gets 'Completed' but always stays 'Queued'. They also do not get re-created, so AsyncApexJob.CreatedDate is identical to CronTrigger.StartTime.

So my question: How do I get the "before" value of PreviousFireTime? For relatively easy CronExpressions that define recurring triggers (say 0 0 * * * ? or 0 0 0 * * ?) I could calculate the difference between PreviousFireTime and NextFireTime and simply substract this difference from PreviousFireTime to "calculate" an actual previous time. However, this fails for any more complex CronExpressions (say a Job that executes once every hour during business hours (from 8 AM to 8 PM) and if I try to calculate this on the first and last run).

Answer 1

I decided to implement a CronHandler that can be constructed from either a cron id, a scheduled job name or a list of configs that allows me to calculate a Last Execution Time based on a couple of premises.

• For inits with a trigger id (i.e. from SchedulableContext), only one CronTrigger record is initialized
• For inits with a list of CronTrigger records or a Job Name (best used with wildcards), all matching records are initialized

The API DateTime getLastExection() iterates all configs in the handler and returns the latest DateTime value there is. A cron config object can handle the following states:

1. EXECUTING and no recognized pattern for a recurring job: Returns null
2. EXEUCTING and recognized pattern for a recurring job: Returns a calculated previous fire Time based on distance to the next fire time (with a small 10% overlap)
3. NOT EXECUTING (i.e. WAITING, etc): Returns the PreviousFireTime from CronTrigger object. May be null, if the trigger never ran.

This is enough to:

• Evaluate an actual previous fire time directly from SOQL for a series of crons, as long as I have a similar naming schema ('Job #1', 'Job #2', etc). Does not require the crons to be scheduled symmetrical (e.g. 0,15,30,45)
• Estimate the last fire time for a single cron as long as the cron is scheduled to run regularily ('0 0 * * * ?', ...). This works for hourly and daily scheduled jobs equally.

I would be happy to hear your feedback and suggestions to improve the code.

Here's the class:

 public class CronHandler {

    @testVisible private List<CronConfiguration> CronConfigs;

    public CronHandler(Id cronTriggerId) {
        this([SELECT Id,StartTime,EndTime,PreviousFireTime,NextFireTime,TimesTriggered,CronExpression,State,CronJobDetail.Name FROM CronTrigger WHERE Id = :cronTriggerId]);
    }

    public CronHandler(String jobName) {
        this([SELECT Id,StartTime,EndTime,PreviousFireTime,NextFireTime,TimesTriggered,CronExpression,State,CronJobDetail.Name FROM CronTrigger WHERE CronJobDetail.Name LIKE :jobName]);
    }

    public CronHandler(List<CronTrigger> triggerConfigs) {
        CronConfigs = new List<CronHandler.CronConfiguration>();
        for (CronTrigger ct : triggerConfigs) CronConfigs.add(new CronConfiguration(ct));
    }

    // DESCRIPTION
    //      simply forwards the size of all internal configs that are known
    public Integer getCronConfigSize() {
        return CronConfigs.size();
    }

    // DESCRIPTION
    //      Returns the highest value for previous fire time for all configs that are known to the handler
    //      The configs itself dynamically evaluate if they are to a executing or a waiting cron job
    //      And return their actual previous fire time or a calculated estimator
    // RETURN
    //      DateTime: The highest previous fire time of all configs
    public DateTime getLastExecution() {
        List<DateTime> ExecutionDateTimes = new List<DateTime>();
        for (CronHandler.CronConfiguration cc : CronConfigs) ExecutionDateTimes.add(cc.getPreviousFireTime());
        ExecutionDateTimes.sort();
        return ExecutionDateTimes.isEmpty() ? null : ExecutionDateTimes.get(ExecutionDateTimes.size() - 1);
    }

    public class CronConfiguration {

        public String CronExpression;
        public String State;
        public String CronDetailName;
        public DateTime PreviousFireTime;
        public DateTime NextFireTime;
        public Integer TimesTriggered;

        // blank constructor to emulate a cron config for testing
        public CronConfiguration() { }

        // load configuration from a CronTrigger record
        public CronConfiguration(CronTrigger ct) {
            CronExpression = ct.CronExpression;
            State = ct.State;
            CronDetailName = ct.CronJobDetail.Name;
            PreviousFireTime = ct.PreviousFireTime;
            NextFireTime = ct.NextFireTime;
            TimesTriggered = ct.TimesTriggered;
        }

        // DESCRIPTION
        //      Returns the best estimator for this CronJob's last execution. If trigger is not executing, returns the actual PreviousFireTime.
        //      Otherwise, calculates the estimated previous fire time based on cron expression and next fire time.
        // RETURN
        //      DateTime: Best guessed previous fire time for the cron config
        public DateTime getPreviousFireTime() {
            if (State != 'EXECUTING') return PreviousFireTime;
            if (matchesRecurringJobExpression(CronExpression)) return DateTime.newInstance(PreviousFireTime.getTime() - getTimeDifference(NextFireTime, PreviousFireTime));
            return null;
        }

        // DESCRIPTION
        //      Calculates the difference in milliseconds between two dateTimes.
        //      The difference is then multiplied by 10%, to account for inaccuracies
        // PARAMETERS
        //      DateTime dtNext: expected to be the higher of both values
        //      DateTime dtPrev: expected to be the lower of both values
        // RETURN
        //      Long: Difference between both datetime values with 10% inaccuracy
        private Long getTimeDifference(DateTime dtNext, DateTime dtPrev) {
            return ((dtNext.getTime() - dtPrev.getTime())*1.1).longValue();
        }

        // DESCRIPTION
        //      Splits the cron expression in its part and validates each list-element individually
        //      Only validates elements where wildards may appear (hour and up) and exists with false
        // RETURN
        //      Boolean: Evaluation, if expression represents a regular recurring job
        private Boolean matchesRecurringJobExpression(String cronExp) {
            List<String> splitExpression = cronExp.split(' ');
            // hours (3rd element): fix numbers or recurring is accepted
            if (!Pattern.matches('[0-9]{1,2}|\\*', splitExpression.get(2))) return false;
            // days of month (4th element): only recurring / arbitrary is accepted
            if (!Pattern.matches('\\*|\\?', splitExpression.get(3))) return false;
            // month (5th element): only recurring is accepted
            if (!Pattern.matches('\\*', splitExpression.get(4))) return false;
            // day of week (6th element): only recurring / arbitrary is accepted
            if (!Pattern.matches('\\*|\\?', splitExpression.get(5))) return false;
            return true;
        }
    }
}

And here are some tests:

@isTest
public class T_CronHandler {

    @isTest
    static void initHandlerByName_NoCronConfigurationsExisting_NoConfigsLoaded() {
        CronHandler ch = new CronHandler('Unknown Test Config');
        System.assertEquals(0, ch.getCronConfigSize(), 'Configs have been initiated, but expecting 0');
    }

    @isTest
    static void initHandlerById_NoCronConfigurationsExisting_NoConfigsLoaded() {
        // SETUP
        // this is a syntactically valid cron trigger id that should never exist 
        Id invalidId = '08e4E00000XXXXXXXX';
        CronHandler ch = new CronHandler(invalidId);
        // VERIFY
        System.assertEquals(0, ch.getCronConfigSize(), 'Configs have been initiated, but expecting 0');        
    }

    @isTest
    static void initHandlerByName_SingleCronConfigurationExisting_OneConfigLoaded() {
        // SETUP
        // schedule the Dispatcher_ScheduledSends
        System.schedule('Test Send #1', '0 0 * * * ?', new Dispatcher_ScheduledSends());
        CronHandler ch = new CronHandler('Test Send%');
        // VERIFY
        System.assertEquals(1, ch.getCronConfigSize(), 'Wrong number of cron configs initiated');
    }

    @isTest
    static void initHandlerByName_MultipleCronConfigurationExisting_AllConfigsLoaded() {
        // SETUP
        // schedule the Dispatcher_ScheduledSends
        System.schedule('Test Send #1', '0 0 * * * ?', new Dispatcher_ScheduledSends());
        System.schedule('Test Send #3', '0 15 * * * ?', new Dispatcher_ScheduledSends());
        System.schedule('Test Send #2', '0 30 * * * ?', new Dispatcher_ScheduledSends());
        System.schedule('Test Send #4', '0 45 * * * ?', new Dispatcher_ScheduledSends());
        CronHandler ch = new CronHandler('Test Send%');
        // VERIFY
        System.assertEquals(4, ch.getCronConfigSize(), 'Wrong number of cron configs initiated');
    }

    @isTest
    static void initHandlerByName_MultipleCronConfigurationOnDifferentNamesExisting_OnlyMatchingConfigsLoaded() {
        // SETUP
        // schedule the Dispatcher_ScheduledSends
        System.schedule('First Send #1', '0 0 * * * ?', new Dispatcher_ScheduledSends());
        System.schedule('First Send #3', '0 15 * * * ?', new Dispatcher_ScheduledSends());
        System.schedule('Another Send #1', '0 30 * * * ?', new Dispatcher_ScheduledSends());
        System.schedule('Another Send #2', '0 45 * * * ?', new Dispatcher_ScheduledSends());
        CronHandler ch = new CronHandler('First Send%');
        // VERIFY
        System.assertEquals(2, ch.getCronConfigSize(), 'Wrong number of cron configs initiated');
    }

    @isTest
    static void getLastExecution_NoConfigLoaded_Null() {
        CronHandler ch = new CronHandler('Unknown Test Config');
        System.assertEquals(null, ch.getLastExecution(), 'Wrong value for last execution');
    }

    @isTest
    static void getLastExecution_ExecutingCronInitialized_PrevFromExecuting() {
        CronHandler ch = new CronHandler('Unknown Test Config');
        // ACTION
        CronHandler.CronConfiguration cc = new CronHandler.CronConfiguration();
        cc.State = 'EXECUTING';
        cc.CronExpression = '0 0 * * * ?';
        cc.PreviousFireTime = DateTime.newInstance(2019, 05, 06, 07, 23, 57);
        cc.NextFireTime = DateTime.newInstance(2019, 05, 06, 08, 23, 55);
        ch.CronConfigs.add(cc);
        // VERIFY
        System.assertNotEquals(null, ch.getLastExecution(), 'Last execution is null');
        System.assertEquals(cc.getPreviousFireTime(), ch.getLastExecution(), 'Wrong value for last execution');
    }

    @isTest
    static void getLastExecution_MultipleHourlyCrons_WaitingHasExecutions_PrevFromWaiting() {
        CronHandler ch = new CronHandler('Unknown Test Config');
        // ACTION
        // setup config for executing job
        CronHandler.CronConfiguration cc1 = new CronHandler.CronConfiguration();
        cc1.State = 'EXECUTING';
        cc1.CronExpression = '0 0 * * * ?';
        cc1.PreviousFireTime = DateTime.newInstance(2019, 05, 06, 07, 00, 2);
        cc1.NextFireTime = DateTime.newInstance(2019, 05, 06, 08, 00, 00);
        ch.CronConfigs.add(cc1);
        // setup config for waiting job
        CronHandler.CronConfiguration cc2 = new CronHandler.CronConfiguration();
        cc2.State = 'WAITING';
        cc2.CronExpression = '0 30 * * * ?';
        cc2.PreviousFireTime = DateTime.newInstance(2019, 05, 06, 06, 30, 05);
        cc2.NextFireTime = DateTime.newInstance(2019, 05, 06, 07, 30, 00);
        ch.CronConfigs.add(cc2);
        // VERIFY
        System.assertNotEquals(null, ch.getLastExecution(), 'Last execution is null');
        System.assertEquals(cc2.getPreviousFireTime(), ch.getLastExecution(), 'Wrong value for last execution');
    }

    @isTest
    static void getLastExecution_MixedHourlyAndDailyCrons_WaitingHasExecutions_PrevFromWaiting() {
        CronHandler ch = new CronHandler('Unknown Test Config');
        // ACTION
        // setup config for executing job
        CronHandler.CronConfiguration cc1 = new CronHandler.CronConfiguration();
        cc1.State = 'EXECUTING';
        cc1.CronExpression = '0 0 * * * ?';
        cc1.PreviousFireTime = DateTime.newInstance(2019, 05, 06, 07, 00, 2);
        cc1.NextFireTime = DateTime.newInstance(2019, 05, 06, 08, 00, 00);
        ch.CronConfigs.add(cc1);
        // setup config for waiting job
        CronHandler.CronConfiguration cc2 = new CronHandler.CronConfiguration();
        cc2.State = 'WAITING';
        cc2.CronExpression = '0 30 23 * * ?';
        cc2.PreviousFireTime = DateTime.newInstance(2019, 05, 05, 23, 30, 05);
        cc2.NextFireTime = DateTime.newInstance(2019, 05, 06, 23, 30, 00);
        ch.CronConfigs.add(cc2);
        // VERIFY
        System.assertNotEquals(null, ch.getLastExecution(), 'Last execution is null');
        System.assertEquals(cc1.getPreviousFireTime(), ch.getLastExecution(), 'Wrong value for last execution');
    }

    @isTest
    static void getLastExecution_WaitingHourlyCrons_NoExecutions_Null() {
        CronHandler ch = new CronHandler('Unknown Test Config');
        // ACTION
        // setup config for executing job
        CronHandler.CronConfiguration cc1 = new CronHandler.CronConfiguration();
        cc1.State = 'WAITING';
        cc1.CronExpression = '0 0 * * * ?';
        cc1.NextFireTime = DateTime.newInstance(2019, 05, 07, 00, 00, 00);
        ch.CronConfigs.add(cc1);
        // setup config for waiting job
        CronHandler.CronConfiguration cc2 = new CronHandler.CronConfiguration();
        cc2.State = 'WAITING';
        cc2.CronExpression = '0 30 * * * ?';
        cc2.NextFireTime = DateTime.newInstance(2019, 05, 07, 0, 30, 00);
        ch.CronConfigs.add(cc2);
        // VERIFY
        System.assertEquals(null, ch.getLastExecution(), 'Last execution is null');
    }

    @isTest
    static void cronConfig_getPreviousFireTime_NotExecuting_InternalValueReturned() {
        // SETUP
        // emulate a cron config for a waiting trigger
        CronHandler.CronConfiguration cc = new CronHandler.CronConfiguration();
        cc.PreviousFireTime = DateTime.newInstance(2019, 05, 06, 07, 23, 57);
        cc.NextFireTime = DateTime.newInstance(2019, 05, 06, 08, 23, 55);
        // ACTION & VERIFY
        cc.State = 'WAITING';
        System.assertEquals(DateTime.newInstance(2019, 05, 06, 07, 23, 57), cc.getPreviousFireTime(), 'Wrong previous fire time returned for WAITING');
        cc.State = 'PAUSED';
        System.assertEquals(DateTime.newInstance(2019, 05, 06, 07, 23, 57), cc.getPreviousFireTime(), 'Wrong previous fire time returned for PAUSED');
        cc.State = 'ACQUIRED';
        System.assertEquals(DateTime.newInstance(2019, 05, 06, 07, 23, 57), cc.getPreviousFireTime(), 'Wrong previous fire time returned for ACQUIRED');
        cc.State = 'COMPLETE';
        System.assertEquals(DateTime.newInstance(2019, 05, 06, 07, 23, 57), cc.getPreviousFireTime(), 'Wrong previous fire time returned for COMPLETED');
        cc.State = 'ERROR';
        System.assertEquals(DateTime.newInstance(2019, 05, 06, 07, 23, 57), cc.getPreviousFireTime(), 'Wrong previous fire time returned for ERROR');
        cc.State = 'BLOCKED';
        System.assertEquals(DateTime.newInstance(2019, 05, 06, 07, 23, 57), cc.getPreviousFireTime(), 'Wrong previous fire time returned for BLOCKED');
        cc.State = 'PAUSED_BLOCKED';
        System.assertEquals(DateTime.newInstance(2019, 05, 06, 07, 23, 57), cc.getPreviousFireTime(), 'Wrong previous fire time returned for PAUSED_BLOCKED');
    }

    @isTest
    static void cronConfig_getPreviousFireTime_NotExecutingNoRuns_RecurringCronExpression_InternalValueReturned() {
        // SETUP
        // emulate a cron config for a waiting trigger
        CronHandler.CronConfiguration cc = new CronHandler.CronConfiguration();
        cc.State = 'WAITING';
        cc.CronExpression = '0 30 * * * ?';
        cc.NextFireTime = DateTime.newInstance(2019, 05, 06, 08, 30, 00);
        // ACTION & VERIFY
        System.assertEquals(null, cc.getPreviousFireTime(), 'Wrong value for WAITING');
    }

    @isTest
    static void cronConfig_getPreviousFireTime_Executing_RecurringCronExpression_CalculatedPrevTime() {
        // SETUP
        // emulate a cron config for a waiting trigger
        CronHandler.CronConfiguration cc = new CronHandler.CronConfiguration();
        cc.State = 'EXECUTING';
        cc.PreviousFireTime = DateTime.newInstance(2019, 05, 06, 07, 23, 57);
        cc.NextFireTime = DateTime.newInstance(2019, 05, 06, 08, 23, 55);
        // ACTION & VERIFY
        // calculate a DateTime with 10% bonus in difference
        DateTime expectedVal = DateTime.newInstance(cc.PreviousFireTime.getTime() - ((cc.NextFireTime.getTime() - cc.PreviousFireTime.getTime())*1.1).longValue());
        cc.CronExpression = '0 0 * * * ?';
        System.assertEquals(expectedVal, cc.getPreviousFireTime(), 'Wrong value for: 0 0 * * * ?');
        cc.CronExpression = '0 0 * * * *';
        System.assertEquals(expectedVal, cc.getPreviousFireTime(), 'Wrong value for: 0 0 * * * *');
        cc.CronExpression = '12 33 * * * ?';
        System.assertEquals(expectedVal, cc.getPreviousFireTime(), 'Wrong value for: 12 33 * * * ?');
        cc.CronExpression = '0 0 5 * * ?';
        System.assertEquals(expectedVal, cc.getPreviousFireTime(), 'Wrong value for: 0 0 5 * * ?');
        cc.CronExpression = '59 59 23 ? * * *';
        System.assertEquals(expectedVal, cc.getPreviousFireTime(), 'Wrong value for: 59 59 23 ? * *');
    }

    @isTest
    static void cronConfig_getPreviousFireTime_Executing_NonrecurringCronExpression_Null() {
        // SETUP
        // emulate a cron config for a waiting trigger
        CronHandler.CronConfiguration cc = new CronHandler.CronConfiguration();
        cc.State = 'EXECUTING';
        cc.PreviousFireTime = DateTime.newInstance(2019, 05, 06, 07, 23, 57);
        cc.NextFireTime = DateTime.newInstance(2019, 05, 06, 08, 23, 55);
        // ACTION & VERIFY
        cc.CronExpression = '0 0 * 1/5 * ?';
        System.assertEquals(null, cc.getPreviousFireTime(), 'Wrong value for: 0 0 * 1/5 * ?');
        cc.CronExpression = '12 33 22 ? * MON-FRI';
        System.assertEquals(null, cc.getPreviousFireTime(), 'Wrong value for: 12 33 22 ? * MON-FRI');

    }
}