MySQL查询获取每小时的记录 [英] MySQL query to get hour wise records

问题描述

hi.，

我在MySQL中有一个表，该表具有3列，分别称为Name，Number，DateInserted.在DateInserted列中，我要插入日期和时间.现在我想按以下格式每小时获取一次插入的记录

从小时-到小时-计数
11-12-358

10-11-55

9-10-112

8-9-15

7-8-856

6-7-558

5-6-556

4-5-458

3-4-741

2-3-125

1-2-789

0-1-856


感谢ALL ..

..

hi.,

i have a table in MySQL having 3 columns called Name,Number,DateInserted. In DateInserted column i m inserting the date and time. now i want to get the records inserted by every hour means in the following format

FromHour - ToHour - counts
11 - 12 - 358

10 - 11 - 55

9 - 10 - 112

8 - 9 - 15

7 - 8 - 856

6 - 7 - 558

5 - 6 - 556

4 - 5 - 458

3 - 4 - 741

2 - 3 - 125

1 - 2 - 789

0 - 1 - 856


thanks to ALL..

..

推荐答案

这是 ^ ]函数.您可以将其用作GROUP字段，如下所示:

here is a HOUR[^] function in MySQL. You can use it as GROUP field, like this:

select HOUR(DateInserted) as FromHour, count(*) from tabelname where ...
group by HOUR(DateInserted)

由于HOUR从1:00到1:59会给您1，因此您必须加1才能获得ToHour.

As HOUR will give you 1 from 1:00 till 1:59, you yous have to add 1 to get ToHour.

这是您的示例，其余的取决于您

This is an example for you, rest is upto you

create table def(d DATETIME);
    -> insert into def values
    -> ('2012-01-02 12:10:11'),
    -> ('2012-01-02 12:12:11'),
    -> ('2012-01-02 01:12:11'),
    -> ('2012-01-02 01:32:11');
SELECT COUNT(*) FROM def GROUP BY MID(d, 1, 13);

自己找出休息机会

Find out rest by yourself

这篇关于MySQL查询获取每小时的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！