cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - If community subscription notifications are filling up your inbox you can set up a daily digest and get all your notifications in a single email. X

How to use aggregate service[MIN ,MAX ,AVERAGE ,COUNT ,SUM] on DateTime

done11
1-Newbie

How to use aggregate service[MIN ,MAX ,AVERAGE ,COUNT ,SUM] on DateTime

Hi

I want to use the aggregates MIN method on TimeStamp[BaseType : DateTime] column

I want output as below:

UserIDTimeStamp
12016-11-16 24:57:27.412
22016-11-16 20:53:28.444
32016-11-16 19:52:26.234

I am taking input as QueryPropertyHistory Service data.

var params = {

  t: MyInfotableInput /* INFOTABLE */,

  columns: "TimeStamp" /* STRING */,

  aggregates: 'MIN' /* STRING */,

  groupByColumns: 'UserID' /* STRING */

};

// result: INFOTABLE

var result = Resources["InfoTableFunctions"].Aggregate(params);

1 ACCEPTED SOLUTION

Accepted Solutions

You can use getTime() service on DateTime object to get the milliseconds from EPOC time and then you can easily set back to DateTime object with new Date(milliseconds).

You can use the Derive snippet to generate the myDateTimeField.getTime() column.

View solution in original post

5 REPLIES 5

The aggregate functions only work with numbers.
What you can do is make a temporary infotable with an additional column and calculate the date difference between the timestamp and a fixed date using dateDifference(date1, date2).
This results in a number where the aggregate functions can be used.

You can use getTime() service on DateTime object to get the milliseconds from EPOC time and then you can easily set back to DateTime object with new Date(milliseconds).

You can use the Derive snippet to generate the myDateTimeField.getTime() column.

The way Carles Coll​ mentioned worked pretty well form me.

done11
1-Newbie
(To:done11)

Thanks alot Carles Coll​ . It worked !

If your issue is resolved please mark this thread as answered with the right comment/answer.

Top Tags