Count distinct of a date field (sys_created_on)

[MR Carvalho]

Hi guys,

I'm creating a script to make a distinct count of the field created, I tried the script below but it's not working because field created contains date, hour, minute and only need the date, for example: 30/03/2023

Someone could help me with the code to take out hour, minute of the field created and then make the distinct count?

That's my code.

var ga = new GlideAggregate('incident');

ga.addEncodedQuery("caller_id=javascript:gs.getUserID()^active=true^universal_requestISEMPTY");

ga.addAggregate('COUNT(DISTINCT', 'sys_created_on');

ga.setGroup(false);

ga.query();

ga.next();

gs.print(ga.getAggregate('COUNT(DISTINCT', 'sys_created_on'));

Thanks in advance.

[Benjamin A · Accepted solution]

Hi MR Carvalho,

The sys_created_on field is of type GlideDateTime, so directly querying for distinct dates will not work, as you've seen. The data needs to be manipulated to remove the time component and then count the distinct dates. To my knowledge, this can not be done via an aggregate query.

However, while not very efficient, an option would be to do the following: 

// Initiate a new GlideRecord of the 'incident' table
var gr = new GlideRecord('incident');

// Create a blank query 
gr.query();

// Create an empty object to store unique dates and their occurrences.
var dates = {};

// Iterate over all incident records
while(gr.next()) {

    //Get the 'sys_created_on' field of the current record
    var createdOn = new GlideDateTime(gr.sys_created_on.getDisplayValue());

    //Extract only the date of the 'sys_created_on' field by splitting the display value at the space and keeping only the first part
    var dateOnly = createdOn.getDisplayValue().split(' ')[0]; 

    // Check if the date is already in our 'dates' object
    if (!dates[dateOnly]) {

       // If it's a new date, add it to the 'dates' object and set the count to 1.
        dates[dateOnly] = 1;

    } else {

        // If it's a date we've already seen, increment the count by 1.
        dates[dateOnly] += 1;

    }
}

// Print out the date and its corresponding count.
for (var date in dates) {
    gs.print('Date: ' + date + ', Count: ' + dates[date]);
}

Thanks,

Ben