CODEWITHSUNDEEP
javascriptphpmysqlcharts
Radical_Activity
Radical_Activity

Reputation: 2738

How to generate Date/Time values for a chart dynamically?

I have a big set of data for the past few years in the MySQL database. Each of these rows has a Timestamp column. This is what I'd like to use for the x-axis within the chart.

What I'd like to do is to break up these dates into logical groups and display the chart to the user with the correct date and summarized data associated to that timeframe. The user will be able to select date ranges dynamically.

What I'd like is something similar: for example, when the user selects Today (or a one-day frame) I want the software to break up the data into 2-hour blocks which is 12 data sets (I only have space for 12). However, if they select 2 days I want to break that up into 12 equal pieces as well. If they select 15 days, 3 years, etc I still want the script to break up the data into 12 pieces (while summarizing the data of the row).

Here is an example of the outcome I'd like to achieve somehow dynamically. I can use MySQL, PHP and Javascript for this. The following format is just a representation it can be an array, object, anything:

This is the data I have in the DB for example:

id  |   value   |   date                |
-----------------------------------------
1   |   12      |   2017-01-23 00:13:12 |
2   |   54      |   2017-01-23 08:33:23 |
3   |   45      |   2017-01-23 09:01:57 |
4   |   94      |   2017-01-22 05:17:15 |
5   |   67      |   2017-01-22 10:12:44 |
6   |   3       |   2017-01-22 22:56:54 |
7   |   44      |   2017-01-22 23:27:55 |
8   |   19      |   2017-01-21 15:02:23 |
9   |   8       |   2017-01-21 00:14:54 |

This is what I want to have before sending the data to my chart:

id  |   value   |   date                |
-----------------------------------------
1   |   12      |   2017-01-23 06:00:00 |
2   |   99      |   2017-01-23 12:00:00 |
3   |   0       |   2017-01-23 18:00:00 |
4   |   0       |   2017-01-23 23:59:59 |
5   |   94      |   2017-01-22 06:00:00 |
6   |   67      |   2017-01-22 12:00:00 |
7   |   0       |   2017-01-22 18:00:00 |
8   |   47      |   2017-01-21 23:59:59 |
9   |   8       |   2017-01-21 06:00:00 |
10  |   0       |   2017-01-21 12:00:00 |
11  |   19      |   2017-01-21 18:00:00 |
12  |   0       |   2017-01-21 23:59:59 |

Again, it doesn't matter what's the date range, the script should automatically split it into 12 equal pieces.

Any idea how to achieve this? I'm seeing a lot of these type of charts on the internet, and there must be an easy way of doing this - not just coding the conditions manually.

Upvotes: 0

Views: 517

Answers (2)

trincot
trincot

Reputation: 350272

You could do this in SQL, PHP and JavaScript. The choice is really a matter of personal preference. I will give here a PHP function to do it:

function group_data($data, $count) {
    usort($data, function ($a, $b) {
        return strcmp($a["date"], $b["date"]);
    });
    $result = [];
    $first = new DateTime($data[0]["date"]);
    $first->setTime(0, 0); // First group always starts at midnight
    $last = new DateTime($data[count($data)-1]["date"]);
    // Group size is always an integer number of hours:
    $hours = ceil(($first->diff($last)->days + 1) * 24 / $count);
    $group_interval = new DateInterval("PT{$hours}H");
    while ($count--) { // for each group
        $first->add($group_interval); // Increment the date with the fixed group size
        $date = $first->format('Y-m-d H:i:s');
        // Accumulate the values in the data that precede this date/time:
        $value = 0;
        while (current($data) && current($data)["date"] < $date) {
            $value += current($data)["value"];
            next($data); // Move to the next record
        }
        // Append this group to the result
        $result[] = ["id" => count($result)+1, "value" => $value, "date" => $date];
    }
    return $result;
}

Here is an example of how to use it. I'll assume you already have the selected the data from the database into an associative array:

$data = [
    ["id" => 1, "value" => 12, "date" => "2017-01-23 00:13:12"],
    ["id" => 2, "value" => 54, "date" => "2017-01-23 08:33:23"],
    ["id" => 3, "value" => 45, "date" => "2017-01-23 09:01:57"],
    ["id" => 4, "value" => 94, "date" => "2017-01-22 05:17:15"],
    ["id" => 5, "value" => 67, "date" => "2017-01-22 10:12:44"],
    ["id" => 6, "value" =>  3, "date" => "2017-01-22 22:56:54"],
    ["id" => 7, "value" => 44, "date" => "2017-01-22 23:27:55"],
    ["id" => 8, "value" => 19, "date" => "2017-01-21 15:02:23"],
    ["id" => 9, "value" =>  8, "date" => "2017-01-21 00:14:54"],
];

// Use the function to divide the data into 12 equally sized groups    
$result = group_data($data, 12);

The $result variable will have the following data after running the above example:

[
    ["id" =>  1, "value" =>  8, "date" => "2017-01-21 06:00:00"],
    ["id" =>  2, "value" =>  0, "date" => "2017-01-21 12:00:00"],
    ["id" =>  3, "value" => 19, "date" => "2017-01-21 18:00:00"],
    ["id" =>  4, "value" =>  0, "date" => "2017-01-22 00:00:00"],
    ["id" =>  5, "value" => 94, "date" => "2017-01-22 06:00:00"],
    ["id" =>  6, "value" => 67, "date" => "2017-01-22 12:00:00"],
    ["id" =>  7, "value" =>  0, "date" => "2017-01-22 18:00:00"],
    ["id" =>  8, "value" => 47, "date" => "2017-01-23 00:00:00"],
    ["id" =>  9, "value" => 12, "date" => "2017-01-23 06:00:00"],
    ["id" => 10, "value" => 99, "date" => "2017-01-23 12:00:00"],
    ["id" => 11, "value" =>  0, "date" => "2017-01-23 18:00:00"],
    ["id" => 12, "value" =>  0, "date" => "2017-01-24 00:00:00"],
]

Upvotes: 2

Matthew Lagerwey
Matthew Lagerwey

Reputation: 120

It sounds like you just need to create an array of the entire data set like: SQL SELECT * FROM your table WHERE (date BETWEEN date1 AND date2); //send data set where you want it Business logic Array = [...dataset]; function (Array) { let length= Array.length; let sectionLength = Math.floor(length / 12); for ( let i =0; i < length; i++) { // create bins if ( i < sectionLength){ //code to summarize array goes here or create new array } if ( i >= sectionLength && i < (section length * 2 )) { // 2nd bin } ...etc then have to solve for remainder cases because which can be between 0 and 12 cases exclusive so bin distribution will be different `function( array1, array2, array3...etc { foreach (argument) {

display(argument) }` This is just pseudo code but hopefully you get the idea

Upvotes: 1

Related Questions