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

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

SOLVED
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

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

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

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

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.

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

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

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

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

Highlighted

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

Thanks alot Carles Coll​ . It worked !

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

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