question Field type "schedule_date_time" in Developer forum

Field type "schedule_date_time"

carl_hensley — Wed, 07 Dec 2011 06:32:38 GMT

I'm trying to write a glide record query against the cmn_schedule_span table and want to restrict the records returned by the start_date_time and the end_date_time on this table. I'm using the data to write a schedule page timeline.

My script is having problems as the start_date_time and end_date_time are of type "schedule_date_time".

I tried this and many variants, but could not get the desired result within the gliderecord:
(for example tried using a GlideDate, etc)

var _userID = '46d44a23a9fe19810012d100cca80666';
var start = new GlideDateTime('2011-10-19 20:00:00');
var end = new GlideDateTime('2012-05-01 07:17:42');
var schedRec = new GlideRecord('cmn_schedule_span');
schedRec.addQuery('schedule.u_schedule_user', '=', _userID);
schedRec.addQuery('Type', '=', 'Time off');
schedRec.addQuery('start_date_time', '<=', end); // constrain the leave to the time bounds of
schedRec.addQuery('end_date_time', '>=', start); // the task we are allocating.
schedRec.query();
while (schedRec.next()) {
.....process the selected records.
}

I then tried to use an encoded query, and was surprised to see the only filter criteria I could use was, "is", "is not" or "is anything".

So given I was suitably stumped, I ended up returning all records for the complete data range, and then used script to only process the records I desired:

var _userID = '46d44a23a9fe19810012d100cca80666';
var start = new GlideDateTime('2011-10-19 20:00:00');
var end = new GlideDateTime('2012-05-01 07:17:42');
var schedRec = new GlideRecord('cmn_schedule_span');
schedRec.addQuery('schedule.u_schedule_user', '=', _userID);
schedRec.addQuery('Type', '=', 'Time off');
schedRec.query();
while (schedRec.next()) {
var leaveStart = schedRec.start_date_time.getGlideObject().getGlideDateTime();
var leaveEnd = schedRec.end_date_time.getGlideObject().getGlideDateTime();
if (( leaveStart <= end) && (leaveEnd >= start)) {
.....process the selected records.
}
}

While my second script provides the correct data, I'm concerned the runtime is longer than it should be, as it is returning more from the database than it needs to.

Can anyone help???

Re: Field type "schedule_date_time"

w_bouwer — Wed, 07 Dec 2011 10:33:10 GMT

You can see differences between the data in the two fields if you run the following script in the background.



var schedRec = new GlideRecord('cmn_schedule_span');
schedRec.query();
if(schedRec.next()){
    gs.print(new GlideDateTime('2011-10-19 20:00:00').getValue());
    gs.print(schedRec.start_date_time);
}

The way the data is stored in the fields differs greatly, as well as their object definition. I worked up a bit of script to make this work:



function timeISOConvertor(x){
x = x.getRaw();
var dt = new Packages.java.text.SimpleDateFormat();
dt.applyPattern('yyyy');
var ye = dt.format(x).toString();
dt.applyPattern('MM');
var mo = dt.format(x).toString();
dt.applyPattern('dd');
var da = dt.format(x).toString();
dt.applyPattern('H');
var ho = dt.format(x).toString();
dt.applyPattern('m');
var mi = dt.format(x).toString();
dt.applyPattern('ss');
var se = dt.format(x).toString();

var ret = ye+mo+da+'T'+ho+mi+se;
return ret;
}

Now if you try it you will see the difference. Just make sure you parse your GlideDateTime with the function, and your good to go.




var x = timeISOConvertor(new GlideDateTime('2011-07-19 20:00:10'));
var schedRec = new GlideRecord('cmn_schedule_span');
schedRec.addQuery('start_date_time', '<=', x);
schedRec.query();

while(schedRec.next()){
    gs.print(schedRec.start_date_time); //resulting data
}

Hope it helps,

Wesley

Re: Field type "schedule_date_time"

carl_hensley — Wed, 07 Dec 2011 22:41:29 GMT

Works like a treat.

Thankyou very much!

Re: Field type "schedule_date_time"

Tue, 13 Dec 2011 11:57:18 GMT

Note that a "schedule_date_time" can't always be precisely represented by a GlideDateTime, because they don't don't usually specify a specific timezone. Most Schedules are defined as "floating".

When you convert one into a GlideDateTime you are 'fixing' it according to your own session timezone.

Re: Field type "schedule_date_time"

carl_hensley — Tue, 13 Dec 2011 21:33:40 GMT

Thanks James,

That is good to know.

cheers,
carl

Re: Field type "schedule_date_time"

jtshone — Tue, 15 Oct 2024 15:23:03 GMT

Any idea why it is failing to correctly convert these dates:

Values on change record:

DEBUG JS: start date = 26/10/2024 08:26:06 PM

DEBUG JS: end date = 30/10/2024 08:26:08 PM

ISO conversion - setting date to the day before:

DEBUG JS: iso start date = 20241025T17000

DEBUG JS: iso end date = 20241029T17000