dongyi8416 2010-07-01 00:28
浏览 58
已采纳

在PHP中按小时计算出现次数

I have a script that polls a MySQL database (Nagios, specifically) for the date/time of events. I want to count how many events occur in each hour based upon all of the data my script returns.

Here is what the results look like: (each on their own line)


2010-03-01 03:20:26
2010-02-28 19:07:26
2010-02-28 00:50:37
2010-02-27 17:07:35
2010-02-27 17:06:35

Here is the bash script that I use to get what I'm looking for:


  cat temp |awk '{print $2 $0}' | cut -d: -f1 | sort | uniq -c |sort -k2 -n

Here are some sample results:



 21 00
     32 01
     31 02
     46 03
     34 04
     12 05
     11 06
      8 07
    107 08
     56 09
     16 10
     55 11
     50 12
     33 13
     23 14
     34 15
     11 16
     18 17
     14 18
     25 19
      9 20
      5 21
     38 22
     20 23

So, you can see from the above results that the little bash command parses my PHP script' s output and returns how many occurrences in column 1 and what hour of the day they occurred at in column 2.

I need to incorporate this into my PHP script.

Here is my code right now that retrieves it from the Nagios DB:

$query2= "SELECT * FROM nagios_statehistory WHERE ".
    "object_id='$tosearch' AND output='CRITICAL - Socket timeout " .
    "after 10 seconds' ORDER BY state_time DESC";

$result2 = mysql_query($query2) or die (mysql_error());

while($row = mysql_fetch_array($result2)) {
    $outtie = substr( $row['state_time'] , 0 , 10);
    if ($outtie !== $lastout) {
        //bolds the first occurrence of each day
        echo "<b>";
    }
    /* print the actual time.  This is what I'm looking
     * to count the occurrences of */
    echo $row['state_time'];
    $lastout = substr( $row['state_time'] , 0 , 10);
    //disable bold and insert a line break
    echo "</b><br />";
}
  • 写回答

1条回答 默认 最新

  • doudai3012 2010-07-01 00:57
    关注
    SELECT COUNT(*) AS `cnt`, HOUR(`state_time`) AS `hr` FROM nagios_statehistory
    WHERE object_id='$tosearch' AND output='CRITICAL - Socket timeout after 10 seconds'
    GROUP BY `hr`
    ORDER BY state_time DESC
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决