ServiceNow Query: Records created last week

[RahulY00040]

Hello friends,

Hope you are all doing good !

I have a scheduled job running at 1200 AM Monday, every week. Script within Scheduled Job queries records created in table "Last Week". Scheduled job has been running for a while but recent execution queried records not from last week, it did from week before last week that I did not want to happen. Is there any reason for this? Anyone has idea? Should I change the schedule from 1200 AM to anything after it?

Thanks,
Rahul

[harshkumbhani]

Hello Rahul,

Could you please share the script and the configuration details? I may have an idea what could be causing the issue. 

Thanks,
Harsh

[RahulY00040]

Thanks Harsh for reaching out. PFB. Mind that its scheduled to run on Monday 1200 AM.

var ga=new GlideAggregate('incident');

ga.addEncodedQuery('sys_created_onONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()'); 

ga.addAggregate('COUNT');

ga.query();

var arr=[];

while(ga.next())

{

arr.push(ga.getValue('number'));

}

gs.info('Numbers are '+arr);

Thanks,
Rahul

[Its_Sagnic · Accepted solution]

Hi @RahulY00040 ,

Hope you are doing well.

The issue you are describing is a common "race condition" in ServiceNow scheduled jobs, typically caused by a mismatch between the execution time and the system's interpretation of "last week."

At 12:00 AM Monday, the system might still be processing the "current" week as the one that just ended or already transitioned into the "new" week depending on millisecond-level timing or timezone offsets. 

Why this is happening

1. Timezone Offsets: If your "Run as" user or system timezone has even a slight offset, 12:00 AM Monday might technically still be Sunday 11:59 PM in the system's "internal" time. When a query for "Last Week" runs on Sunday, it returns the week before last.
2. Relative Date Calculation: Methods like gs.weeksAgoStart() or encoded queries for "Last Week" calculate based on the exact moment the script executes. If the job triggers at 12:00:00, it is on the knife-edge of the week transition.
3. Run As User: Scheduled jobs run in the timezone of the user specified in the Run as field. If that user's timezone is different from yours, the "Monday" start time may not be what you expect. 

The Solution :

You should change the schedule time to at least 01:00 AM or 02:00 AM on Monday. This ensures the system has fully transitioned into the new week across all possible timezone interpretations and provides a buffer for any system lag. 

Recommended Fixes :

• Update the Schedule: Move the "Time" on your Scheduled Script Execution to 01:00:00.
• Hardcode the Query: Instead of relying on the relative "Last Week" filter, use a script to explicitly define the date range. You can use the GlideDateTime API to calculate exactly 7 days back from the current execution time.
• Verify "Run as tz": Check the Run as tz field on the scheduled job. If it is empty, it uses the "Run as" user's timezone. You can force it to a specific timezone (e.g., US/Eastern or GMT) to ensure consistency. 

If you find the solution helpful Please mark it as helpful and accept the solution.

Regards,

Sagnic