Sort Multidimensional Array by Date Column, Then Use Other Column Values If Dates Are the Same

Sort multidimensional array by date column, then use other column values if dates are the same

Update

I recently answered this question in a much more capable manner in the "definitive" topic on sorting multidimensional arrays. You can safely skip reading the rest of this answer and directly follow the link for a much more capable solution.

Original answer

The function uasort lets you define your own comparison function. Simply put all the criteria you want inside that.

For example, to sort by birthday and then by name:

function comparer($first, $second) {
    // First see if birthdays differ
    if ($first['birthday'] < $second['birthday']) {
        return -1;
    }
    else if ($first['birthday'] > $second['birthday']) {
        return 1;
    }

    // OK, birthdays are equal. What else?
    if ($first['name'] < $second['name']) {
        return -1;
    }
    else if ($first['name'] > $second['name']) {
        return 1;
    }

    // No more sort criteria. The two elements are equal.
    return 0;
}

I am ignoring the fact that in your example, the birthdays are not in a format that can be ordered by a simple comparison using the operator <. In practice you would convert them to a trivially-comparable format first.

Update: if you think that maintaining a bunch of these multiple-criteria comparers could get ugly real fast, you find me in agreement. But this problem can be solved as any other in computer science: just add another level of abstraction.

I 'll be assuming PHP 5.3 for the next example, in order to use the convenient anon function syntax. But in principle, you could do the same with create_function.

function make_comparer() {
    $criteriaNames = func_get_args();
    $comparer = function($first, $second) use ($criteriaNames) {
        // Do we have anything to compare?
        while(!empty($criteriaNames)) {
            // What will we compare now?
            $criterion = array_shift($criteriaNames);

            // Do the actual comparison
            if ($first[$criterion] < $second[$criterion]) {
                return -1;
            }
            else if ($first[$criterion] > $second[$criterion]) {
                return 1;
            }

        }

        // Nothing more to compare with, so $first == $second
        return 0;
    };

    return $comparer;
}

You could then do:

uasort($myArray, make_comparer('birthday', 'name'));

This example possibly tries to be too clever; in general I don't like to use functions that do not accept their arguments by name. But in this case, the usage scenario is a very strong argument for being too clever.

PHP Sort a multidimensional array by element containing Y-m-d H:i:s date

Use usort() and a custom comparison function:

function date_compare($a, $b)
{
    $t1 = strtotime($a['datetime']);
    $t2 = strtotime($b['datetime']);
    return $t1 - $t2;
}    
usort($array, 'date_compare');

---

EDIT: Your data is organized in an array of arrays. To better distinguish those, let's call the inner arrays (data) records, so that your data really is an array of records.

usort will pass two of these records to the given comparison function date_compare() at a a time. date_compare then extracts the "datetime" field of each record as a UNIX timestamp (an integer), and returns the difference, so that the result will be 0 if both dates are equal, a positive number if the first one ($a) is larger or a negative value if the second argument ($b) is larger. usort() uses this information to sort the array.

PHP array sort by value and date (2 columns)

The second usort overrides the output of the first usort.

What you need to do is merge the 2 sorting functions to order themselves by title and then date time.

Pseudocode would be :

function SortByTitleAndDateTime($a,$b)
    1) SortByTitle and return the value if it is non zero.
    2) If SortByTitle is zero (meaning the 2 values are the same in terms of title), 
        do SortByDateTime

In the end, you'd only need to call usort(array, SortByTitleAndDateTime).

PHP custom sort function of multidimensional array because of usort members equality problem

foreach( $arr as $i => &$x ){ $x['k'] = $i; }

This adds a column to contain the key you're wanting to sort by. Then, just do your usort operation, that falls back to the key compare if date/time is equal. I added a simple helper function to fix your dates so strtotime can parse it.

function o_to_time( $o ){
    return strtotime( implode( "/" , array_reverse( explode( "/" , $o["date"] ) ) ) . " " . $o["time"] );
}

usort( $arr , function( $a , $b ){
    return o_to_time( $b ) <=> o_to_time( $a ) ?: $a['k'] <=> $b['k'];
} );

JS sort array by two columns of Dates

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")))

Sort a range or array based on two columns that contain the date and time

Given your comment - you want to sort the written chunk - you have two methods available. One is to sort written data after writing, by using the Spreadsheet service's Range#sort(sortObject) method. The other is to sort the data before writing, using the JavaScript Array#sort(sortFunction()) method.

Currently, your sort code //regWeek.sort([{ column: 1, ascending: true }]); is attempting to sort a JavaScript array, using the sorting object expected by the Spreadsheet service. Thus, you can simply chain this .sort(...) call to your write call, as Range#setValues() returns the same Range, allowing repeated Range method calling (e.g. to set values, then apply formatting, etc.).

This looks like:

ss.getRange(ss.getLastRow() + 1, 2, regWeek.length, regWeek[0].length)
   .setValues(regWeek)
   /* other "chainable" Range methods you want to apply to
       the cells you just wrote to. */
   .sort([{column: 1, ascending: true}, ...]);

Here I have updated the range you access to reference the data you are attempting to write - regWeek - so that it is always the correct size to hold the data. I've also visually broken apart the one-liner so you can better see the "chaining" that is happening between Spreadsheet service calls.

The other method - sorting before writing - will be faster, especially as the size and complexity of the sort increases. The idea behind sorting a range is you need to use a function that returns a negative value when the first index's value should come before the second's, a positive value when the first index's value should come after the second's, and a zero value if they are equivalent. This means a function that returns a boolean is NOT going to sort as one thinks, since false and 0 are equivalent in Javascript, while true and 1 are also equivalent.

Your sort looks like this, assuming regWeek is an array of arrays and you are sorting on numeric values (or at least values which will cast to numbers, like Dates).

regWeek.sort(function (r1, r2) {
  // r1 and r2 are elements in the regWeek array, i.e.
  // they are each a row array if regWeek is an array of arrays:
  // Sort ascending on the first column, which is index 0:
  // if r1[0] = 1, r2[0] = 2, then 1 - 2 is -1, so r1 sorts before r2
  return r1[0] - r2[0];
});

I strongly recommend reviewing the Array#sort documentation.

How to sort a two-dimensional array (2d array) by date in ruby on rails?

You can sort 2d arrays with sort_by method: sort_by sorts in ascending order by default. If you want it to be in descending order, just reverse the result.

array.sort_by{ |a| a.first }.reverse

Sorting by key in a multidimensional array with php

Generic solution to sort arrays of arrays with multiple keys

Based on my answer to this question, here is a very generic solution that you can use in lots of situations.

Limitation: Requires PHP >= 5.3 to work, due to the presence of anonymous functions.

New and improved, now with descending sort support

function make_comparer() {
    $criteriaNames = func_get_args();
    $comparer = function($first, $second) use ($criteriaNames) {
        // Do we have anything to compare?
        while(!empty($criteriaNames)) {
            // What will we compare now?
            $criterion = array_shift($criteriaNames);

            // Used to reverse the sort order by multiplying
            // 1 = ascending, -1 = descending
            $sortOrder = 1; 
            if (is_array($criterion)) {
                $sortOrder = $criterion[1] == SORT_DESC ? -1 : 1;
                $criterion = $criterion[0];
            }

            // Do the actual comparison
            if ($first[$criterion] < $second[$criterion]) {
                return -1 * $sortOrder;
            }
            else if ($first[$criterion] > $second[$criterion]) {
                return 1 * $sortOrder;
            }

        }

        // Nothing more to compare with, so $first == $second
        return 0;
    };

    return $comparer;
}

How to use it

To sort by year ascending:

uasort($array, make_comparer('Year'));

To sort by year ascending, then by month ascending:

uasort($array, make_comparer('Year', 'Month'));

To sort by year descending, then by month ascending:

uasort($array, make_comparer(array('Year', SORT_DESC), 'Month'));

This last one is what you 're after.

---

Related Topics