JS sort array by two columns of Dates

Question

I am trying to sort an multidimensional array by dates in two columns. Each element of the array looks like that [Thu Feb 25 10:00:00 GMT-05:00 2021, Tue Mar 09 10:00:00 GMT-05:00 2021, 1326M98301, 1326M98301, product 3, 1.0, 2.188120526039E12]

I want to sort it first by the second column and then by the first. Lets say that first column is as order day and the second is delivery day so I just want to know which should be shipped first. I guess that's the simplest way I can explain it...

So the output should look like this:

var result = [
  ["Fri Feb 12 10:00:00 GMT-05:00 2021", "Mon Mar 15 11:00:00 GMT-04:00 2021", "1216991601", "1.216991601E9", "product 13", "8.0", "2.13203250127E12"],
  ["Wed Feb 03 10:00:00 GMT-05:00 2021", "Mon Mar 15 11:00:00 GMT-04:00 2021", "1326N10901", "1326N10901", "product 14", "1.0", "2.132640068139E12"],
  ["Sun Feb 14 10:00:00 GMT-05:00 2021", "Mon Mar 15 11:00:00 GMT-04:00 2021", "1326N13701", "1326N13701", "product 15", "1.0", "2.132640068139E12"],
  ["Mon Feb 15 10:00:00 GMT-05:00 2021", "Mon Mar 15 11:00:00 GMT-04:00 2021", "1326N11601", "1326N11601", "product 16", "1.0", "2.132640068139E12"],
  ["Wed Mar 03 10:00:00 GMT-05:00 2021", "Mon Mar 15 11:00:00 GMT-04:00 2021", "1326N14401", "1326N14401", "product 17", "1.0", "2.132640068139E12"],
  ["Wed Mar 03 10:00:00 GMT-05:00 2021", "Tue Mar 16 11:00:00 GMT-04:00 2021", "1326N12301", "1326N12301", "product 18", "1.0", "2.132640068139E12"],
  ["Wed Mar 03 10:00:00 GMT-05:00 2021", "Tue Mar 16 11:00:00 GMT-04:00 2021", "1326N13001", "1326N13001", "product 19", "1.0", "2.132640068139E12"],
  ["Thu Mar 04 10:00:00 GMT-05:00 2021", "Wed Mar 17 11:00:00 GMT-04:00 2021", "1326N12201", "1326N12201", "product 20", "1.0", "2.132022060039E12"],
  ["Sat Mar 06 10:00:00 GMT-05:00 2021", "Wed Mar 17 11:00:00 GMT-04:00 2021", "1326N15001", "1326N15001", "product 21", "1.0", "2.132022060039E12"],
  ["Sat Mar 06 10:00:00 GMT-05:00 2021", "Wed Mar 17 11:00:00 GMT-04:00 2021", "1326N14301", "1326N14301", "product 22", "1.0", "2.132022060039E12"],
  ["Sat Mar 06 10:00:00 GMT-05:00 2021", "Sat Mar 20 11:00:00 GMT-04:00 2021", "1326N15701", "1326N15701", "product 23", "1.0", "2.132022060039E12"]
]

Maybe the screenshot will be easier to look at:

So, I managed to write a code that do the job but only for one month and only one day. This is the point where I realized that now I would have to write different variables for every day of the month which is not good I guess... and of course not every month has exactly 30 days, so there would be an error some day... I have now idea how could I solve this problem.

My approach was:

From whole array take only one month - column 2 (original data has more than one month)
Create an array for each day of that month
Sort array of a day (by the first column)
Join all sorted arrays

If anyone would like to help me with this problem I would appreciate it.

My code so far:

var values = [
  ["Wed Feb 24 10:00:00 GMT-05:00 2021", "Mon Mar 08 10:00:00 GMT-05:00 2021", "1326G43001", "1326G43001", "product 2", "1.0", "2.144509151159E12"],
  ["Thu Feb 25 10:00:00 GMT-05:00 2021", "Tue Mar 09 10:00:00 GMT-05:00 2021", "1326M98301", "1326M98301", "product 3", "1.0", "2.188120526039E12"],
  ["Thu Feb 25 10:00:00 GMT-05:00 2021", "Tue Mar 09 10:00:00 GMT-05:00 2021", "1326M98401", "1326M98401", "product 4", "1.0", "2.188120526039E12"],
  ["Tue Sep 10 11:00:00 GMT-04:00 2019", "Wed Mar 10 10:00:00 GMT-05:00 2021", "1235K42001", "1235K42001", "product 5", "5.0", "2.13265002505E12"],
  ["Wed Dec 16 10:00:00 GMT-05:00 2020", "Wed Mar 10 10:00:00 GMT-05:00 2021", "1236109001", "1.236109001E9", "product 6", "1.0", "2.13264008516E12"],
  ["Wed Mar 03 10:00:00 GMT-05:00 2021", "Wed Mar 10 10:00:00 GMT-05:00 2021", "1326N04201", "1326N04201", "product 7", "1.0", "2.132022501039E12"],
  ["Mon Jan 25 10:00:00 GMT-05:00 2021", "Thu Mar 11 10:00:00 GMT-05:00 2021", "1296125801", "1.296125801E9", "product 8", "80.0", "2.116103080499E12"],
  ["Mon Feb 01 10:00:00 GMT-05:00 2021", "Thu Mar 11 10:00:00 GMT-05:00 2021", "1256202001", "1.256202001E9", "product 9", "4.0", "2.13851701218E12"],
  ["Sun Feb 07 10:00:00 GMT-05:00 2021", "Thu Mar 11 10:00:00 GMT-05:00 2021", "1216792301", "1.216792301E9", "product 10", "4.0", "2.13202036113E12"],
  ["Mon Feb 08 10:00:00 GMT-05:00 2021", "Thu Mar 11 10:00:00 GMT-05:00 2021", "1216792501", "1.216792501E9", "product 11", "4.0", "2.13202036113E12"],
  ["Wed Feb 24 10:00:00 GMT-05:00 2021", "Thu Mar 11 10:00:00 GMT-05:00 2021", "1326J51601", "1326J51601", "product 12", "1.0", "2.132033501239E12"],
  ["Tue Mar 09 10:00:00 GMT-05:00 2021", "Thu Mar 11 10:00:00 GMT-05:00 2021", "1326G50401", "1326G50401", "product 13", "1.0", "2.138509248889E12"],
  ["Mon May 25 11:00:00 GMT-04:00 2020", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1326013001", "1.326013001E9", "product 14", "2.0", "2.138512062259E12"],
  ["Mon May 25 11:00:00 GMT-04:00 2020", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1326013201", "1.326013201E9", "product 15", "2.0", "2.138512062259E12"],
  ["Sun Aug 30 11:00:00 GMT-04:00 2020", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1326791401", "9.320001591E9", "product 16", "1.0", "2.14571400837E12"],
  ["Sun Nov 08 10:00:00 GMT-05:00 2020", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1376039201", "1.376039201E9", "product 17", "50.0", "2.132032001239E12"],
  ["Sun Dec 20 10:00:00 GMT-05:00 2020", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1326G02901", "1326G02901", "product 18", "5.0", "2.14570705404E12"],
  ["Thu Dec 24 10:00:00 GMT-05:00 2020", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1326448103", "1.326448103E9", "product 19", "10.0", "2.11610205877E12"],
  ["Wed Jan 13 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1296468601", "1.296468601E9", "product 20", "5.0", "2.14450906398E12"],
  ["Wed Jan 13 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1296468701", "1.296468701E9", "product 21", "5.0", "2.14450906398E12"],
  ["Sun Jan 17 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1256635601", "9.251000093E9", "product 22", "1.0", "2.18710602275E12"],
  ["Mon Jan 18 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1296122701", "1.296122701E9", "product 23", "1.0", "2.13265000541E12"],
  ["Mon Jan 18 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1296122801", "1.296122801E9", "product 24", "1.0", "2.13265000541E12"],
  ["Tue Jan 19 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1326104501", "1.326104501E9", "product 25", "1.0", "2.14571014417E12"],
  ["Thu Jan 21 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1296124001", "1.296124001E9", "product 26", "1.0", "2.18711202795E12"],
  ["Sun Jan 31 10:00:00 GMT-05:00 2021", "Sun Mar 14 11:00:00 GMT-04:00 2021", "1326M66101", "1326M66101", "product 27", "10.0", "2.11610301556E12"]
]

function sortFunc() {

  // sort by second column
  compare_dates = function(date1, date2) {

    d1 = new Date(date1[1]);
    d2 = new Date(date2[1]);
    if (d1 > d2) return 1;
    else if (d1 < d2) return -1;
    else return 0;
  }

  const sorted = values.sort(compare_dates);

  // get months of second column
  function months(arr) {
    var nowa = [];
    for (var i = 0; i < arr.length; i++) {
      d = new Date(arr[i][1]);
      nowa.push(d.getMonth());
    }
    return nowa;
  };

  const miesiace = months(sorted);
  //Logger.log(miesiace);


  // remove duplicates

  function onlyUnique(value, index, self) {
    return self.indexOf(value) === index;
  }
  const duplicates = miesiace.filter(onlyUnique);
  console.log(duplicates);


  function firstMonth(arr, month) {
    var nowa = [];
    for (var i = 0; i < arr.length; i++) {
      d = new Date(arr[i][1]);
      if (d === month) {
        nowa.push(arr[i]);
      }
    }
    return nowa;
  }

  const first = firstMonth(sorted, duplicates[0]);
  console.log(first);


  // indexes of the first month in array
  function getAllIndexes(arr, val) {
    var indexes = [],
      i = -1;
    while ((i = arr.indexOf(val, i + 1)) != -1) {
      indexes.push(i);
    }
    return indexes;
  }

  var indexes = getAllIndexes(miesiace, duplicates[0]);


  nowiutka = [];
  for (var i = 0; i < indexes.length; i++) {
    nowiutka.push(sorted[indexes[i]]);
  }


  //get days
  function days(arr) {
    var nowa = [];
    for (var i = 0; i < arr.length; i++) {
      d = new Date(arr[i][1]);
      nowa.push(d.getDate());
    }
    return nowa;
  };

  const dni = days(nowiutka);


  const dniUnique = dni.filter(onlyUnique);


  //get indexes of the x day
  const dniIndex = getAllIndexes(dni, dniUnique[2]);

  //create new array of products from indexes above
  var ostateczna = [];

  for (var i = 0; i < dniIndex.length; i++) {
    if (dniIndex.length === 0) {
      ostateczna.push(nowiutka[0])
    } else {
      ostateczna.push(nowiutka[dniIndex[i]]);
    }
  }

  // sort result
  const sortedResult = ostateczna.sort(compare_dates);



  //---- Input Result----//
  console.log(sortedResult);
}
sortFunc()

Please click edit, then [<>] snippet editor and create aminimal reproducible example - there is far too much irrelevant sheet code here — mplungjan
– mplungjan, Commented Mar 15, 2021 at 7:42
@mplungjan I did but there is something wrong and I have no idea what — Timonek
– Timonek, Commented Mar 15, 2021 at 7:58
About I want to sort it first by second column and the by the first., I cannot understand it. I apologize for this. If your sample image is the input situation, can you provide the sample output situation? And, in your situation, how about sorting the Spreadsheet instead of the values retrieved from the Spreadsheet? But if this was not the direction you expect, I apologize. — Tanaike
– Tanaike, Commented Mar 15, 2021 at 8:08
@Tanaike it means that as a first step it should be sorted by second column and next step will be sorting it by first column of the array. The image is the sample output. Already sorted array. Not sorted array you can find in snippet. Yes you could do this with spreadsheet function but it takes a lot of time to sort all data. At first you sort second column ascending and then you have to select only one day of column 2 and sort by column 1 — Timonek
– Timonek, Commented Mar 15, 2021 at 8:16

Terry Lennox · Accepted Answer · 2021-03-15 08:51:17Z

We can create a sortDates function to sort the dates, using Date.parse to get the unix time for each date, then compare to first by column 2, then by column 1.

   const values = [
['Wed Feb 24 10:00:00 GMT-05:00 2021', 'Mon Mar 08 10:00:00 GMT-05:00 2021', '1326G43001', '1326G43001', 'product 2', 1.0, 2.144509151159E12], 
['Thu Feb 25 10:00:00 GMT-05:00 2021', 'Tue Mar 09 10:00:00 GMT-05:00 2021', '1326M98301', '1326M98301', 'product 3', 1.0, 2.188120526039E12],
['Thu Feb 25 10:00:00 GMT-05:00 2021', 'Tue Mar 09 10:00:00 GMT-05:00 2021', '1326M98401', '1326M98401', 'product 4', 1.0, 2.188120526039E12],
['Tue Sep 10 11:00:00 GMT-04:00 2019', 'Wed Mar 10 10:00:00 GMT-05:00 2021', '1235K42001', '1235K42001', 'product 5', 5.0, 2.13265002505E12],
['Wed Dec 16 10:00:00 GMT-05:00 2020', 'Wed Mar 10 10:00:00 GMT-05:00 2021', '1236109001', 1.236109001E9, 'product 6', 1.0, 2.13264008516E12],
['Wed Mar 03 10:00:00 GMT-05:00 2021', 'Wed Mar 10 10:00:00 GMT-05:00 2021', '1326N04201', '1326N04201', 'product 7', 1.0, 2.132022501039E12],
['Mon Jan 25 10:00:00 GMT-05:00 2021', 'Thu Mar 11 10:00:00 GMT-05:00 2021', 1296125801, 1.296125801E9, 'product 8', 80.0, 2.116103080499E12],
['Mon Feb 01 10:00:00 GMT-05:00 2021', 'Thu Mar 11 10:00:00 GMT-05:00 2021', 1256202001, 1.256202001E9, 'product 9', 4.0, 2.13851701218E12],
['Sun Feb 07 10:00:00 GMT-05:00 2021', 'Thu Mar 11 10:00:00 GMT-05:00 2021', 1216792301, 1.216792301E9, 'product 10', 4.0, 2.13202036113E12],
['Mon Feb 08 10:00:00 GMT-05:00 2021', 'Thu Mar 11 10:00:00 GMT-05:00 2021', 1216792501, 1.216792501E9, 'product 11', 4.0, 2.13202036113E12],
['Wed Feb 24 10:00:00 GMT-05:00 2021', 'Thu Mar 11 10:00:00 GMT-05:00 2021', '1326J51601', '1326J51601', 'product 12', 1.0, 2.132033501239E12],
['Tue Mar 09 10:00:00 GMT-05:00 2021', 'Thu Mar 11 10:00:00 GMT-05:00 2021', '1326G50401', '1326G50401', 'product 13', 1.0, 2.138509248889E12],
['Mon May 25 11:00:00 GMT-04:00 2020', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1326013001, 1.326013001E9, 'product 14', 2.0, 2.138512062259E12],
['Mon May 25 11:00:00 GMT-04:00 2020', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1326013201, 1.326013201E9, 'product 15', 2.0, 2.138512062259E12], 
['Sun Aug 30 11:00:00 GMT-04:00 2020', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1326791401, 9.320001591E9, 'product 16', 1.0, 2.14571400837E12], 
['Sun Nov 08 10:00:00 GMT-05:00 2020', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1376039201, 1.376039201E9, 'product 17', 50.0, 2.132032001239E12],
['Sun Dec 20 10:00:00 GMT-05:00 2020', 'Sun Mar 14 11:00:00 GMT-04:00 2021', '1326G02901', '1326G02901', 'product 18', 5.0, 2.14570705404E12],
['Thu Dec 24 10:00:00 GMT-05:00 2020', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1326448103, 1.326448103E9, 'product 19', 10.0, 2.11610205877E12],
['Wed Jan 13 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1296468601, 1.296468601E9, 'product 20', 5.0, 2.14450906398E12], 
['Wed Jan 13 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1296468701, 1.296468701E9, 'product 21', 5.0, 2.14450906398E12], 
['Sun Jan 17 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1256635601, 9.251000093E9, 'product 22', 1.0, 2.18710602275E12],
['Mon Jan 18 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1296122701, 1.296122701E9, 'product 23', 1.0, 2.13265000541E12],
['Mon Jan 18 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1296122801, 1.296122801E9, 'product 24', 1.0, 2.13265000541E12],
['Tue Jan 19 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1326104501, 1.326104501E9, 'product 25', 1.0, 2.14571014417E12],
['Thu Jan 21 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', 1296124001, 1.296124001E9, 'product 26', 1.0, 2.18711202795E12],
['Sun Jan 31 10:00:00 GMT-05:00 2021', 'Sun Mar 14 11:00:00 GMT-04:00 2021', '1326M66101', '1326M66101', 'product 27', 10.0, 2.11610301556E12]]

function sortDates(arr) {
    return [...arr].sort((a, b) => {
        return Date.parse(a[1]) - Date.parse(b[1]) 
            || Date.parse(a[0]) - Date.parse(b[0]);
    })
}

console.log("Sorted result:")
console.log("Column 1 Date".padEnd(19),'\t', "Column 2 Date");
let sorted = sortDates(values);
sorted.forEach(([a,b]) => console.log(new Date(a).toLocaleString("sv"),'\t', new Date(b).toLocaleString("sv")))

It's short and very neat but I must confess I didn't find it that simple to understand...am I right in thinking that the return statement looks at column 1 of the array in the first part of the logical OR and sorts on that if non-zero, but if it is zero (meaning two elements in column 1 are equal) it looks at column 0 in the second part of the OR and sorts on that?
That's completely correct, if the two values in column 1 are equal, the second part of the OR statement is evaluated.

Abrar Hossain · Accepted Answer · 2021-03-15 08:55:33Z

I think the best approach would be first to group your array into an object that holds your month and for each month, provides a key for each day which is an array. So, you can have something like:

const inp = [
    // order_date, delivery_date, order_no, delivery_no, qty, product_no
    ["2021/02/13", "2021/03/16", 1234, 1221, 5, 21356],
    ["2021/02/14", "2021/03/17", 1234, 1221, 5, 21356],
    ["2021/03/13", "2021/04/16", 1234, 1221, 5, 21356]
];

The above input will create an output:

{
  '2': { '16': [ [Object] ], '17': [ [Object] ] },
  '3': { '16': [ [Object] ] }
}

where, 2 and 3 and months March and April respectively. For March, there are two dates, 16 and 17 and each is an array that holds only one object. In this way, you can basically group all orders by months and then by date for a single year.

Code to create this group object:

const months = {};

inp.forEach(i => {
    const d = new Date(i[1]);
    const m = d.getMonth();

    if(months[`${m}`] && months[`${m}`][`${d.getDate()}`]) {
        // this month already has an entry
        months[`${m}`][`${d.getDate()}`].push({
            productNo: i[5],
            orderNo: i[2],
            deliveryNo: i[3],
            qty: i[4],
            order_date: i[0],
            delivery_date: i[1]
        });
    } else {
        if(!months[`${m}`]) months[`${m}`] = {};
        months[`${m}`][`${d.getDate()}`] = [{
            productNo: i[5],
            orderNo: i[2],
            deliveryNo: i[3],
            qty: i[4],
            order_date: i[0],
            delivery_date: i[1]
        }];
    }
});

The only caveat is that space usage is linear with respect to the problem. Now you can iterate over each key of the months objects where each key is a month, then iterate over each key of months[m] where m is the value of the month in string and get all the keys for that month which are day of the month. Finally, you can sort the day entries month[m][d] where m is month and d is a day of the month.

Hope this helps to provide some idea on how restructure your data.

Collectives™ on Stack Overflow

JS sort array by two columns of Dates

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related