PHP - Sorting an array of arrays by date time.

Hi,

Can anyone suggest the best method to sort an array of arrays based on date time?

Taking the data below, I need the array in oldest to newest. The date/time field is in the format day/month/year hours:minutes:seconds.

I'm potentially looking at sorting up to 4,000 records.

Or, is there a better data structure to use that would make it easier? I'm reading this data from CSV so I can easily change the way its held.

Thank you

Array
(
    [0] => Array
        (
            [0] => 14/03/2015 14:04:36
            [1] => 146
            [2] => 41
            [3] => 00
            [4] => 7
            [5] => 189

        )

    [1] => Array
        (
            [0] => 13/03/2015 01:19:36
            [1] => 146
            [2] => 41
            [3] => 0
            [4] => 4
            [5] => 213

        )

    [2] => Array
        (
            [0] => 13/03/2015 17:34:36
            [1] => 146
            [2] => 41
            [3] => 0
            [4] => 4
            [5] => 323

        )
)

Open in new window

mhdiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

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

Open in new window

Regards
0
Dave BaldwinFixer of ProblemsCommented:
You date format is not sortable because it is in day/month/year format.  One of the reasons for using YYYY-MM-DD format is that it is sortable without changing it.  '14/03/2015 14:04:36' becomes '2015-03-14 14:04:36'.
0
Ray PaseurCommented:
... a better data structure to use that would make it easier?
Yes, there is and you will find it easier to use.  It's the ISO-8601 standard format for date and time.

PHP's ways of handling date and time values are described in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

The ISO-8601 standard removes ambiguity about date and time values.  Considering the sample data above, what is "7?"  There is no context that tells us whether it represents a day or a minute.  Without that, sorting is likely to yield suboptimal results.

Array position zero has all the information you need - it just has to be put in the correct order.  Instead of this:

14/03/2015 14:04:36

... rewrite it like this ...

2015-03-14 14:04:36

That is an ISO-8601 datetime string meaning March 14, 2015 at 2:04:36 pm.  PHP makes it easy to change date / time formats.  The article explains all of this stuff.

Once you have the data in the correct format, you can sort it with any of a variety of PHP algorithms.  If you can post a few more rows of the test data set, I'll be glad to give you a tested and working example showing how to sort them.
0
mhdiAuthor Commented:
Hi all,

I have re-formatted the date/time by splitting it and putting it back together.

$parts = preg_split("/[\s:\/]/", $dateTimeString);
$dateTimeString = $parts[2]."-".$parts[1]."-".$parts[0]." ".$parts[3].":".$parts[4].":".$parts[5];

Open in new window


The data now looks like this.

Array
(
    [0] => Array
        (
            [0] => 2015-03-17 00:04:36
            [1] => 146
            [2] => 41
            [3] => 0
            [4] => X
            [5] => 189
        )

    [1] => Array
        (
            [0] => 2015-03-14 00:19:36
            [1] => 146
            [2] => 41
            [3] => 0
            [4] => X
            [5] => 213
        )

    [2] => Array
        (
            [0] => 2015-03-11 00:34:36
            [1] => 146
            [2] => 41
            [3] => 0
            [4] => X
            [5] => 323
        )

    [3] => Array
        (
            [0] => 2015-03-14 17:49:36
            [1] => 146
            [2] => 78442
            [3] => 0
            [4] => X
            [5] => 95
        )
)

Open in new window

I then modified Rgonzo1971 function to sort it. I think it works.
usort($arrayOfRecords, 'date_compare');
function date_compare($a, $b)
{
    $t1 = strtotime($a['0']);
    $t2 = strtotime($b['0']);
    return $t1 - $t2;
} 

Open in new window


Is that the best way to go about the sorting?

Thank you!
0
Ray PaseurCommented:
You're probably on firm ground with that approach or something very close.  When you use ISO-8601 formats for the dates you do not need to convert the values to timestamps.  The usort() function might look like the one in this example...
http://iconoun.com/demo/temp_mhdi.php

<?php // demo/temp_mhdi.php

/**
 * See: http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28636506.html#a40667014
 */
error_reporting(E_ALL);

$unsorted =
Array
(
    Array
        ( '0' => '2015-03-17 00:04:36'
        , '1' => '146'
        , '2' => '41'
        , '3' => '0'
        , '4' => 'X'
        , '5' => '189'
        ),

    Array
        ( '0' => '2015-03-14 00:19:36'
        , '1' => '146'
        , '2' => '41'
        , '3' => '0'
        , '4' => 'X'
        , '5' => '213'
        ),

    Array
        ( '0' => '2015-03-11 00:34:36'
        , '1' => '146'
        , '2' => '41'
        , '3' => '0'
        , '4' => 'X'
        , '5' => '323'
        ),

    Array
        ( '0' => '2015-03-14 17:49:36'
        , '1' => '146'
        , '2' => '78442'
        , '3' => '0'
        , '4' => 'X'
        , '5' => '95'
        ),
)
;

// A FUNCTION TO COMPARE ELEMENTS OF THE ARRAY
function compareArrayPositionZero($a, $b)
{
    if ($a[0]    == $b[0]) return 0;
    return ($a[0] < $b[0]) ? -1 : 1;
}

// COPY THE ORIGINAL ARRAY AND SORT THE COPY
$sorted = $unsorted;
usort($sorted, 'compareArrayPositionZero');

// SHOW THE ORIGINAL AND THE SORTED VERSION
echo '<pre>';
echo PHP_EOL . 'ORIGINAL:' . PHP_EOL;
print_r($unsorted);

echo PHP_EOL . 'SORTED:' . PHP_EOL;
print_r($sorted);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.