如何动态生成图表的日期/时间值？

wztqucjr 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(315)

我在mysql数据库中有过去几年的大量数据。每一行都有一个timestamp列。这是我想在图表中用的x轴。
我想做的是将这些日期分成逻辑组，并向用户显示具有正确日期和与该时间段相关的汇总数据的图表。用户将能够动态选择日期范围。
我想要的是类似的东西：例如，当用户选择今天（或一天的帧）时，我希望软件将数据分成2小时的块，即12个数据集（我只有12个空间）。但是，如果他们选择2天，我想把它分成12等份以及。如果他们选择15天，3年，等等，我仍然希望脚本将数据分成12部分（同时汇总行的数据）。
下面是一个我想以某种方式动态实现的结果的例子。我可以用mysql，php和javascript来实现这个。以下格式只是一种表示形式，可以是数组、对象或任何形式：
这是我在数据库中的数据，例如：

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 |

这是在将数据发送到图表之前我想要的：

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 |

再说一次，日期范围是多少并不重要，脚本应该自动将其分成12等份。
你知道怎么做到吗？我在互联网上看到了很多这样的图表，必须有一个简单的方法来做到这一点-不仅仅是手动编码的条件。

mysql php JavaScript charts

来源：https://stackoverflow.com/questions/48121914/how-to-generate-date-time-values-for-a-chart-dynamically

2条答案

按热度按时间

jv4diomz1#

听起来您只需要创建一个包含整个数据集的数组，比如：sql SELECT * FROM your table WHERE (date BETWEEN date1 AND date2); //send data set where you want it 业务逻辑 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则必须求解余数情况，因为余数情况可能介于0和12个情况之间，因此bin分布将不同函数（array1、array2、array3…etc{foreach（参数）{ display（argument）}这只是伪代码，希望你能理解

赞(0）回复(0）举报 2021-06-21

beq87vna2#

您可以用sql、php和javascript来实现这一点。选择其实是个人喜好的问题。我将在这里给出一个php函数：

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;
}

下面是一个如何使用它的例子。我假设您已经将数据库中的数据选定为关联数组：

$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);

这个 $result 运行上述示例后，变量将具有以下数据：

[
    ["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"],
]

赞(0）回复(0）举报 2021-06-21

我来回答

如何动态生成图表的日期/时间值？

2条答案

相关问题

热门标签

最新问答