Schedule end datetime is not correct

[Brian Gil]

Hello Community,

I am trying to calculate an end date given a start date and a duration (1 second) using a schedule. I am using the "8-5 weekdays excluding holidays" schedule because I am really trying to calculate the nearest business day, which can include the current day.  

I tried techniques using both DurationCalculator and GlideSchedule APIs, but get unexpected results. Further, I am getting inconsistent results when comparing the two techniques.

I cannot explain this behavior and not sure if this issue is related to the schedule (our schedule was erroneously changed to 10am-7pm), a possible bug in the APIs, or my understanding of the APIs. 

Here is the code for method 1 (DurationCalculator with schedule sys_id removed):

var dates = [
                      '2023-11-10 00:00:00', //business day
                      '2023-11-11 00:00:00' //weekend
                    ];

gs.log("Method 1 (DurationCalculator)..");
for (var i in dates){
    gs.log(i + ".) startDate: " + dates[i]);
    var seconds = 1;
    var startDate = new GlideDateTime(dates[i]);	
    var dc = new DurationCalculator();	
    dc.setSchedule('<sys_id>', 'US/Eastern');
    dc.setStartDateTime(startDate);
    dc.calcDuration(seconds);
    gs.log(i + ".) startDate + 1sec: " + dc.getEndDateTime() + "\n");
}

Here is the code for method 2 (GlideSchedule with schedule sys_id removed):

var dates = [
                      '2023-11-10 00:00:00', //business day
                      '2023-11-11 00:00:00' //weekend
                    ];

gs.log("Method 2 (GlideSchedule)...");
for (var j in dates){
    gs.log(j + ".) startDate: " + dates[j]);
    var milliseconds = 1000;
    var startDate = new GlideDateTime(dates[j]);
    var dur = new GlideDuration(milliseconds);
    var sched = new GlideSchedule('<sys_id>', 'US/Eastern');
    var end = sched.add(startDate, dur);
    gs.log(j + ".) startDate + 1sec: " + end.getDisplayValue() + "\n");
}

Here is a screenshot of the script output:

Method 2 output makes sense since our schedule starts at 10AM.  Where does method 1's 15:00:01 (3PM) come from?  Why does not method 1 and 2 output match?

Here is an additional screenshot where the start-date time was changed to after the 10AM-7PM schedule, namely 20:00:00 (8PM).  Again, these results do not make sense because I would think that after the business day, the result should be the next business day.

To muddy the waters further, when I run gs.calcDateDiff(<date 1>, <date 2>, bool), I also get unexpected results:

gs.log(gs.calDateDiff('2023-11-13 10:00:00', '2023-11-13 15:00:05', false));

Output:

I would expect 5 hours, but the result is 3 hours. Can anyone help explain these anomalies? Thanks in advance.

[Tai Vu]

Hi @Brian Gil 

gs.log(gs.calDateDiff('2023-11-13 10:00:00', '2023-11-13 15:00:05', false));

"I would expect 5 hours, but the result is 3 hours. Can anyone help explain these anomalies?"

It's using the default calendar. You can find on below.

Name: Monday thru Friday (9 - 5)

URL: https://<instance_name>/sys_calendar_list.do)

So it will only calculate the duration inside the defined hours. 

If you're in US Eastern Time Zone => Convert all to UTC, it will be:

2023-11-13 10:00:00 (US/Eastern) => 2023-11-13 14:00:00 (UTC)

2023-11-13 15:00:00 (US/Eastern) => 2023-11-13 19:00:00 (UTC)

Default Calendar (UTC: 09:00 - 17:00) => 17:00:00 - 14:00:00 => 03:00:00

Let's have a look in the API definition. There's couple of points we need to notice.

Cheers,

Tai Vu

[Brian Gil · Accepted solution]

Thanks a bunch Tai Vu. I was able to apply your UTC conversion knowledge to my DurationCalculator problem. 

Essentially, I appended ' 15:00:01' (end of day) to my startDate string, and now the schedule works as expected.  Here is the working example code:

/**
 * Compare a selected date to a start date plus X business days
 * 
 * notes:
 * schedule uses 10-7 schedule and time is converted to UTC 
 * if startDate is Sat/Sun, acts like it is Mon, which works for my use case
 */

var startDate = '2023-11-02 15:00:01';  //use time after end of current day to exclude day
var days = 7;  //business days to add to start date
var selectedDate = '2023-11-13';  //compare this date to startDate + days
var startPlusXDays;

var startGDT = new GlideDateTime(startDate);
var selectedGDT = new GlideDateTime(selectedDate);
selectedGDT = selectedGDT.getDate();  //truncate time for to compare date

var dc = new DurationCalculator();
dc.setSchedule('<schedule_sys_id>', 'US/Eastern');  //schedule excludes weekends and holidays
dc.setStartDateTime(startGDT);
dc.calcDuration(days * 9 * 3600);  //calculate duration in seconds; seconds must be > 0
startPlusXDays = dc.getEndDateTime().getDate();  //truncate time to compare date

gs.info("start date=    " + startDate);
gs.info("+business days=" + days);
gs.info("end date=      " + startPlusXDays.toString());
gs.info("selected date= " + selectedGDT.toString());
gs.info("compare=       " + selectedGDT.compareTo(startPlusXDays));  //returns -1/0/1