Field type "schedule_date_time"

[carl_hensley]

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???

[w_bouwer]

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

[carl_hensley]

Works like a treat.


Thankyou very much!

[#4]

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.

[carl_hensley]

Thanks James,

That is good to know.

cheers,
carl