How to group array of dates by start time and end time and subtract last value - first value?

Question

I have array of dates grouped by day and I want to group dates by time start and end and subtract last value - first value.

The time start and end are also in an array.

Data array:

const data = [
  { x: "2021-10-17T24:00:00.000Z", y: 52 },
  { x: "2021-10-17T22:14:00.000Z", y: 44 },
  { x: "2021-10-17T22:10:00.000Z", y: 36 },
  { x: "2021-10-17T22:00:00.000Z", y: 29 },
  { x: "2021-10-17T20:14:45.540Z", y: 24 },
  { x: "2021-10-17T20:13:45.540Z", y: 20 },
  { x: "2021-10-17T20:00:45.540Z", y: 18 },
  { x: "2021-10-17T14:38:45.540Z", y: 17 },
  { x: "2021-10-17T13:38:45.540Z", y: 15 },
  { x: "2021-09-16T14:36:46.540Z", y: 13 },
  { x: "2021-01-04T14:35:46.540Z", y: 12 },
  { x: "2021-01-01T14:30:46.540Z", y: 10 },
  { x: "2020-02-01T06:28:47.520Z", y: 7 },
  { x: "2020-02-01T07:28:47.520Z", y: 6 },
  { x: "2019-04-13T10:19:20.034Z", y: 5 },
  { x: "2018-01-01T09:09:19.134Z", y: 4 },
  { x: "2017-01-01T12:09:19.034Z", y: 3 },
  { x: "2016-01-02T12:10:20.034Z", y: 2 },
  { x: "2016-01-02T11:10:20.034Z", y: 1 }
];

Time start and end array:

const configurations = [
  {
    label: "C1",
    startTime: { hr: "06", min: "00" },
    endTime: { hr: "22", min: "00" }
  },
  {
    label: "C2",
    startTime: { hr: "22", min: "00" },
    endTime: { hr: "24", min: "00" }
  }
];

Data grouped by day:

[
  {
    "value": 37, // 52 -15 (last value - first value)
    "label": "2021/10/17",
    // (I want to group this data by time start and end)
    "data": [
      {
        "x": "2021-10-17T13:38:45.540Z",
        "y": 15
      },
      {
        "x": "2021-10-17T14:38:45.540Z",
        "y": 17
      },
      {
        "x": "2021-10-17T20:00:45.540Z",
        "y": 18
      },
      {
        "x": "2021-10-17T20:13:45.540Z",
        "y": 20
      },
      {
        "x": "2021-10-17T20:14:45.540Z",
        "y": 24
      },
      {
        "x": "2021-10-17T22:00:00.000Z",
        "y": 29
      },
      {
        "x": "2021-10-17T22:10:00.000Z",
        "y": 36
      },
      {
        "x": "2021-10-17T22:14:00.000Z",
        "y": 44
      },
      {
        "x": "2021-10-17T24:00:00.000Z",
        "y": 52
      }
    ]
  },
//...
]

Expected output :

[
  {
    "value": 37, // 52 -15 (last value - first value)
    "label": "2021/10/17",
    "data": [
              {
                value: 14, // 29-15 =14
                label: "C1"
              },
              {
                value: 23, // 52-29 = 23
                label: "C2"
              }
            ]
  },
 // ...
]

This is what I tried Group data by time start and end.

I'm wondering, 22:00 as end is not included in C1 (24 instead of 29 - 15) but 24:00 is included in C2 (52-29) - that's correct? — Corrl, Oct 28 '21 at 21:48
@Corrl yes, I'm wrong it should be included in C1 and C2. (29-15 =14 not 9) — Dev M, Oct 28 '21 at 21:56

RobG · Answer 1 · 2021-10-29T11:32:24.233

I didn't follow the logic through all your script. I'll just comment that in the following:

parseInt(item.startTime.hr, 10) * 60

parseInt is redundant as multiplication will coerce the string to number.

I've modified the input data to use Date objects not timestamps, and all calculations use UTC.

The following first generates the day grouped data, then processes that for the configuration data. Where there is only one day in a group, the value is 0 since it's the first and last entry. Where there are no days that fit a configuration category, the value is empty so it isn't added to the result data.

One of the forEach loops can be converted to reduce, but sometimes forEach is easier to understand logically. :-)

Hopefully the comments are sufficient, please ask if you need clarification.

let data = [
  { x: new Date('2021-10-17T23:59:59.999Z'), y: 52 },
  { x: new Date('2021-10-17T22:14:00.000Z'), y: 44 },
  { x: new Date('2021-10-17T22:10:00.000Z'), y: 36 },
  { x: new Date('2021-10-17T22:00:00.000Z'), y: 29 },
  { x: new Date('2021-10-17T20:14:45.540Z'), y: 24 },
  { x: new Date('2021-10-17T20:13:45.540Z'), y: 20 },
  { x: new Date('2021-10-17T20:00:45.540Z'), y: 18 },
  { x: new Date('2021-10-17T14:38:45.540Z'), y: 17 },
  { x: new Date('2021-10-17T13:38:45.540Z'), y: 15 },
  { x: new Date('2021-09-16T14:36:46.540Z'), y: 13 },
  { x: new Date('2021-01-04T14:35:46.540Z'), y: 12 },
  { x: new Date('2021-01-01T14:30:46.540Z'), y: 10 },
  { x: new Date('2020-02-01T06:28:47.520Z'), y: 7 },
  { x: new Date('2020-02-01T07:28:47.520Z'), y: 6 },
  { x: new Date('2019-04-13T10:19:20.034Z'), y: 5 },
  { x: new Date('2018-01-01T09:09:19.134Z'), y: 4 },
  { x: new Date('2017-01-01T12:09:19.034Z'), y: 3 },
  { x: new Date('2016-01-02T12:10:20.034Z'), y: 2 },
  { x: new Date('2016-01-02T11:10:20.034Z'), y: 1 }
];

let configurations = [
  {
    label: "C1",
    startTime: { hr: "06", min: "00" },
    endTime: { hr: "22", min: "00" }
  },
  {
    label: "C2",
    startTime: { hr: "22", min: "00" },
    endTime: { hr: "24", min: "00" }
  }
];

function groupByDay(data) {
  // Used in reduce
  let currentDate, group;
  // Group by day
  let dayGroups = data.reduce((acc, item) => {
    // Get the date
    let date = item.x.toISOString().substring(0,10);
    // If a new date, start a new group
    if (date != currentDate) {
      currentDate = date;
      group = {value:'', label:date, data:[]};
      acc.push(group);
    }
    // Add item to start of data, update value
    group.data.unshift(item);
    group.value = group.data[group.data.length - 1].y - group.data[0].y;
    
    return acc;
  }, []);
  
  return dayGroups;
}

function processConfigs(dayGroups, config) {
  // Convert {hr, min} to ms
  let configTimeToMs = ({hr, min}) => hr*3.6e6 + min*6e4;
  // Get ms since start of UTC day
  let dateTimeToMs = (date) => date % 8.64e7;

  // For each dayGroup, collect data for each config
  return dayGroups.reduce((acc, dayGroup) => {
    // Create a new configGroup
    let configGroup = {value:dayGroup.value, label:dayGroup.label, data:[]};
    acc.push(configGroup);
    
    // For each config, add data to configGroup.data
    config.forEach(config => {
      let configStart = configTimeToMs(config.startTime);
      let configEnd = configTimeToMs(config.endTime);
      let configObj = {value: '', label: config.label};

      // Add data for each day
      let groupFirstValue = null;
      dayGroup.data.forEach(day => {
        let dayMs = dateTimeToMs(day.x);
        // If falls in config time range
        if (dayMs >=configStart && dayMs <= configEnd) {
          // If groupFirstValue not already set
          if (!groupFirstValue) {
            groupFirstValue = day.y;
          }
          // Update value
          configObj.value = day.y - groupFirstValue;
        }
      });
      // If value is empty, there are no days for this config so don't add
      if (configObj.value !== '') {
        configGroup.data.push(configObj);
      }
    });
    return acc;
  }, []);
}

let dayGroups = groupByDay(data);
// console.log(dayGroups);
console.log(processConfigs(dayGroups, configurations));

I tried to use some date from database and I got this error `date.substring is not a function` — Dev M, Oct 29 '21 at 09:16
In the OP, `data.x` is a string. If it's actually a Date object, then you need to say that. It is very unusual to have timestamps like "2021-10-17T24:00:00.000Z" rather than "2021-10-18T00:00:00.000Z". That will affect the outcome of processing as the latter would not be included in the data group for 2021-10-17 but the former would. — RobG, Oct 29 '21 at 10:11
maybe this is the correct one "2021-10-18T00:00:00.000Z" in the example I put 24:00:00 because I was confused with the same day or the next day. but in the database I don't think there is a 24:00:00 format. — Dev M, Oct 29 '21 at 10:23
I fixed the error date.substring is not a function: with this line `const [year, month, day]=new Date(iso_string).toISOString('yyyy-MM-ddTHH:mm:ss.sssZ') .split(/\D/g)` but I still have problem with `"value": ""`, It is still empty string. — Dev M, Oct 29 '21 at 10:27
@DevM—it seems *iso_string* is actually a date. If so, change the sample data to `new Date( x: '2021-10-17T22:14:00.000Z')` so the dates are Dates, not strings. *toISOString* doesn't take any arguments so they're ignored. I suspect the time of 24:00:00 is an error in generating the sample data. *split* splits on every match by default so the *g* flag is redundant. Anyway, I'll update the above accordingly. — RobG, Oct 29 '21 at 11:32
mb, configStart and configEnd was NaN for me it was startTime.h now i have some values. — Dev M, Oct 29 '21 at 11:40

How to group array of dates by start time and end time and subtract last value - first value?

1 Answers1