I have a sql query that take the average in seconds between each datetime and group it by interval/10 (to have less precision for groupping I guess) and return the interval with the most occurence.
I would like to do the same in javascript but it seems difficult. Could you help me ? I added the sql in the query and a sample of data to understand more in the snippet.
Thank you
const p_table = [{
"datetime": "2014-11-01 06:52:22.39"
},
{
"datetime": "2014-11-01 06:53:03.13"
},
{
"datetime": "2014-11-01 06:54:04.23"
},
{
"datetime": "2014-11-01 06:57:27.92"
},
{
"datetime": "2014-11-01 06:59:09.76"
},
{
"datetime": "2014-11-01 07:01:32.33"
},
{
"datetime": "2014-11-01 07:02:32.33"
},
{
"datetime": "2014-11-01 07:03:32.33"
},
{
"datetime": "2014-11-01 07:04:32.33"
},
{
"datetime": "2014-11-01 16:54:03.13"
},
{
"datetime": "2014-11-01 16:56:03.13"
},
{
"datetime": "2014-11-01 16:57:03.13"
}
];
/*
SELECT @Interval = AVG(DATEDIFF(Second,p1.DateTime,p2.DateTime))
FROM P_table p1, P_table p2
AND p2.Datetime > p1.Datetime
GROUP BY DATEDIFF(Second,p1.DateTime,p2.DateTime)/10
HAVING Count(*)=MAX(Count(*))
*/
// If I understood the query well the output should be around 60s, as we have 8 time an interval of approx 60s & 1 time an interval of 3600s+
// WIP
var helper = {};
// Count each time an interval occur in values
for (var i = 1; i < p_table.length; i++) {
var d1 = new Date(p_table[i].datetime);
var d2 = new Date(p_table[i - 1].datetime)
var curr_interval_millis = d1 - d2;
var curr_interval_seconds = curr_interval_millis / 1000;
var key = Math.floor(curr_interval_seconds / 10) * 10; // We group by dozen of seconds and then put it back
if (!helper[key]) helper[key] = 1;
else helper[key] += 1;
}
console.log("Count each time an interval occur in values")
console.log(helper);
// Get the interval that occur the most often
var maxKey = -1;
var maxCount = 0;
Object.keys(helper).forEach(key => {
var currMaxCount = helper[key];
if (currMaxCount > maxCount) {
maxKey = key;
maxCount = currMaxCount;
}
});
console.log("max", maxKey, helper[maxKey])
// multiply this interval with the number of lines to have approximate monitoring time per 24h /12h
See Question&Answers more detail:os