Add minutes to time field

[wiltonr]

I have a field on a custom form that is a type of Time.   We are pulling this value in from a SQL database and we need to find a way to add a value from another field that is an integer field pulled in from the same SQL database that represents how many minutes to pad the time field.

So for instance, the time is 1:00pm - the value of this field comes in to Service Now as 1899-12-30 19:00:00 (the SQL database field is 24 hour format and then GMT ??).   All I know is it displays correctly as 1:00pm in Service Now.   The preblock field in the SQL database is minutes and we need to be able to subtract these minutes from the Time field and populate that into a new Time field that we can display on a calendar.

So for instance - the SQL value from the time field coming in is 1899-12-30 19:00:00 and the problock value in the SQL database is 15 (minutes).   We need a way to calculate that the new time field needs to show 12:45pm (or adjust the value of the original field to be 1899-12-30 18:45:00 to display as 12:45pm).

I could maybe do this by writing a ton of code but I'm not even sure if it would be completely correct.   Is anybody great with time calculations and help me out?

Thanks,

Rhonda

[wiltonr]

Shoot, actually I just changed the time on the field in Service Now to be 12:45pm and now the new value is 1970-01-01 18:45:00 rather than what I expected it would be of 1899-12-30 18:45:00 so the original idea that I had is definitely not going to work.   Basically I need to be able to subtract minutes from a time field that displays in this format: 1899-12-30 19:00:00.   Don't think it's going to be simple.  

[edwin_munoz]

Hello Rhonda,

If you are working with time fields, then you can make use of the Date javascript class: Date - JavaScript | MDN

Ex:

var currentDate = current.u_custom_field; //Change field to appropiate name

var minutes = current.minutes_to_substract; //Change field to appropiate name


var millisecondsPerMinute = 60000;



var newDate = new Date(currentDate  - minutes * millisecondsPerMinute);

current.u_custom_field = newDate;

If you are working with Date/Time you can make use of GlideDateTime GlideDateTime - ServiceNow Wiki

Ex:

var gdt = current.u_custom_field; //Change field to appropiate name

var secondsToSubstract = current.minutes_to_substract * 60; //Change field to appropiate name

gdt.addSeconds(secondsToSubstract );

gdt.update;

[wiltonr]

Hi Edwin,

I am working with a time field, and so I tried using your first example but it is returning the new value is Invalid Date.   I did this in a background script:

var preblock = 15;

var gr = new GlideRecord('u_citav_task');

gr.addQuery('number', 'AVTASK0000129');

gr.query();

if(gr.next()){

var currTime = gr.u_event_start_time;

var minutes = preblock;

var millisecondsPerMinute = 60000;

var newTime = new Date(currTime - minutes * millisecondsPerMinute);

gr.u_event_start_time = newTime;

gs.log('currTime var is ' + currTime);

gs.log('minutes var is ' + preblock);

gs.log('newTime is ' + newTime);

}

Result is:

*** Script: currTime var is 1899-12-30 19:00:00

*** Script: minutes var is 15

*** Script: newTime is Invalid Date

[wiltonr]

I might have it - not sure it's efficient though and haven't thoroughly tested yet:

Background script:

var preblock = 15;

var gr = new GlideRecord('u_citav_task');

gr.addQuery('number', 'AVTASK0000129');

gr.query();

if(gr.next()){

gs.print('original time is ' + gr.u_event_start_time);

var gdt = new GlideDateTime(gr.u_event_start_time);

var gtime1 = new GlideTime();

//gtime1.setValue('00:120:00');

gtime1.setValue('00:' + preblock + ':00');

gdt.subtract(gtime1);

gs.print('padding is ' + preblock + ' minutes');

gs.print('padded time is ' + gdt.getTime());

gr.u_event_start_time = gdt.getTime();

gr.update();

}

Results:

*** Script: original time is 1899-12-30 19:00:00

*** Script: padding is 15 minutes

*** Script: padded time is 1970-01-01 18:45:00