How to Orderby for Date field but based on the time only

[Sangeetha8]

Hi all,

I need to sort queried record based on time part only not based on date in Opened  filed .

i have tried the below code it is sorting based on date.

var prob = new GlideRecord('problem');
        query = "sys_update_ondBETWEENjavascript:gs.dateGenerate('" + startDate.getLocalDate() + "','00:00:00')@javascript:gs.dateGenerate('" + endDate.getLocalDate() + "','23:59:59')";

        prob.addEncodedQuery(query);
        prob.orderBy('sys_opened_at');
        prob.query();

Is there any way to do this

Can anyone help me on this

Thanks

[Sai Shravan · Accepted solution]

Hi @Sangeetha8 ,

Below is the background script which I have tried in my PDI

var prob = new GlideRecord('problem');
prob.orderBy('opened_at');
prob.setLimit(10);
prob.query();
while (prob.next()) {
    gs.print(prob.number + ' ' + prob.opened_at);
}

Output

*** Script: PRB0000007 2013-08-15 22:32:07
*** Script: PRB0000002 2013-08-15 22:35:29
*** Script: PRB0000009 2013-08-15 22:54:00
*** Script: PRB0000010 2013-08-15 22:55:28
*** Script: PRB0000001 2015-01-27 23:48:36
*** Script: PRB0000008 2015-01-28 00:06:28
*** Script: PRB0000003 2015-01-28 00:07:08
*** Script: PRB0000005 2015-01-28 00:40:00
*** Script: PRB0000004 2015-01-28 00:40:42
*** Script: PRB0000006 2015-04-06 22:07:07

Note: change the limit the as per your requirement and also change the query.

Regards,

Shravan

Please mark this as helpful and correct answer, if this helps you

[Riya Verma]

Hi @Sangeetha8 ,

Hope you are doing great.

we don't have direct function which can sort the opened only based on time. but we can do so in 2 steps:

1. Use GlideRecord to query the records from the 'problem' table. and store object of remaining query in array

var prob = new GlideRecord('problem');
query = "sys_update_ondBETWEENjavascript:gs.dateGenerate('" + startDate.getLocalDate() + "','00:00:00')@javascript:gs.dateGenerate('" + endDate.getLocalDate() + "','23:59:59')";
prob.addEncodedQuery(query);
prob.query();
var records = [];
while (prob.next()) {
  records.push(prob); // Add each record to the array
}

• Sort the records based on the time part of the 'Opened' field.

records.sort(function(a, b) {
  var aTime = gs.dateGenerate(a.opened.getDisplayValue(), 'time');
  var bTime = gs.dateGenerate(b.opened.getDisplayValue(), 'time');
  return aTime.compareTo(bTime); // Sort in ascending order, modify if needed
});

now record array contains your object in sorted form based on only time, you can iterate the array based on your logic.