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

Forum|Forum|9 years ago
November 16, 2016
5 replies
10279 views

Hi

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

I want output as below:

UserID	TimeStamp
1	2016-11-16 24:57:27.412
2	2016-11-16 20:53:28.444
3	2016-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);

Best answer by CarlesColl

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.

S

sdejager

1-Visitor

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.

C

CarlesColl

Answer

1-Visitor

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.