Creating new schedules by script

[jdchapman]

I recently had the task of generating schedules via script.
This is mostly just to document what finally seems to be working.

When adding a new schedule you must add a new record to the following tables:
cmn_schedule (contains the main schedule record)
cmn_schedule_span (contains the actual time spans that the schedule will use)
cmn_other_schedule (*optional* contains related schedules, like for excluding holidays)

Generating a record for cmn_schedule is trivial, just populate the fields and do a standard insert.

Where I got into trouble though was generating the records for cmn_schedule_span.
The main fields for the span table which take the time start (start_date_time) and time end (end_date_time) use an undocumented field type Schedule Date/Time which further take an undocumented object called GlideScheduleDateTime.

You can construct a GlideScheduleDateTime (GSDT) by passing it a standard GlideDateTime (GDT) object. However, you must pass the GDT object to the GSDT in the Global scope. If you do it in an application scope it truncates the time portion of the GDT.
For example, I would pass it a GDT set to 2018-01-01 11:00:00 in an application scope and the resultant time on the GSDT would be 2018-01-01 00:00:00. If you pass it the same thing in the global scope it will retain the time portion.

However, when you pass the GSDT object a GDT correctly in the global scope it will automatically adjust the time using the timezone set on the GDT. So for example if I were to pass the GSDT a GDT set to 2018-01-01 11:00:00 and the timezone is set to U.S. east coast time (GMT-5) then the resultant time on the GSDT would be 2018-01-01 06:00:00.

There is a way to avoid the auto compensation and that is to pass the GSDT a date time string. You can just do new GlideScheduleDateTime(new GlideDateTime().toString()) to set a GSDT with the current time and it will not auto adjust for whatever time zone the GDT has. Alternatively you can just pass a time string in standard format, new GlideScheduleDateTime("yyyy-mm-dd hh:nn:ss"), and no adjustment should take place.

So what if you're working in an application scope but need to do this? Well, you'll have to create a script include in the global scope which you call from your application scope to construct the GSDT and return it into your application scope.

So after you've correctly constructed a start and end GSDT you just set the fields start_date_time and end_date_time on a cmn_schedule_span GlideRecord and then set the other fields as appropriate ensuring that the new cmn_schedule_span record's schedule field is set with the sys_id for your previously created cmn_schedule record.

After that you can optionally add cmn_other_schedule records relating your new schedule to any others and you should be good to go.