mysql查询数据按时间分组

最新推荐文章于 2024-04-29 15:04:29 发布

原创最新推荐文章于 2024-04-29 15:04:29 发布 · 2.1k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍如何使用MySQL的DATE_FORMAT函数对数据库记录按不同时间粒度（分钟、小时、天、月）进行分组汇总，同时提供PHP实现的五分钟时间间隔数据处理示例，帮助开发者更高效地进行数据统计分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

很多时候想获取数据库中某个时间段得数据数量，这时候就需要对数据进行按时间分组，通常有按分钟分组，按小时分组，按天分组，按月分组

按分钟分组

select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-%d %H:%i') time, COUNT(1) count
                                  from table
                                  GROUP BY time
                                  ORDER BY time desc

2.按小时分组

select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-%d %H:00:00') time, COUNT(1) count
                                  from table
                                  GROUP BY time
                                  ORDER BY time desc

3.按天分组

select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-%d 00:00:00') time, COUNT(1) count
                                  from table
                                  GROUP BY time
                                  ORDER BY time desc

4.按月分组

select DATE_FORMAT(FLOOR(updated_at),'%Y-%m-01 00:00:00') time, COUNT(1) count
                                  from table
                                  GROUP BY time
                                  ORDER BY time desc

这些都有一个规律，只要在DATE_FORMAT函数中将相应的日期格式设置一下，比如按年分组可以这样%Y-01-01 00:00:00，

当然有的时候需要任意时间段的分组，还有需要在没有数据的时间段设置0，mysql也有对应的方法，我是用后端语言php处理的，这里举个按五分钟分组数据的方法

先是按1分钟获取数据

 public static function datasDivideByFiveMinute($count = 12)
    {
        $datas = DB::select("select DATE_FORMAT(FLOOR(rds.updated_at),'%Y-%m-%d %H:%i') time, COUNT(1) count
                                  from registered_device_state
                                  limit ?", [5 * $count]);
        $time = strtotime(date('H:i:00',time()));
        $newDatas = self::datasChange($datas, $count, 5, 'Y-m-d H:i:00',$time);
        return $newDatas;
    }

然后是数据处理

public static function datasChange($datas, $count, $stramp, $format,$time)
    {
        foreach ($datas as $data) {
            $data->time = strtotime($data->time);
        }
        $newDatas = [];
        $newDatas['type'] = $type;
        $newDatas['items'] = [];
        $ptr = 0;
        for ($i = 0; $i < $count; $i++) {
            $temp = $time - $stramp * 60;
            $c = 0;
            for (; $ptr < count($datas); $ptr++) {
                if ($datas[$ptr]->time >= $temp && $datas[$ptr]->time < $time) {
                    $c += $datas[$ptr]->count;
                }
                if ($datas[$ptr]->time < $temp) break;
            }
            array_push($newDatas['items'], [
                'time' => date($format, $time) . '--' . date($format, $temp),
                'count' => $c
            ]);
            $time -= $stramp * 60;
        }
        return $newDatas;
    }

            {
                "time": "2018-09-14 11:44:00--2018-09-14 11:39:00",
                "count": 2
            },
            {
                "time": "2018-09-14 11:39:00--2018-09-14 11:34:00",
                "count": 4
            },
            {
                "time": "2018-09-14 11:34:00--2018-09-14 11:29:00",
                "count": 9
            },