Do the same than this SQL Query but in javascript

Question

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

Answer 1

As you can see in the subject :

    // 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])