Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015
    Posts
    1

    Lightbulb Unanswered: Aggregating MySQL data on hourly basis from minute-wise raw data

    I have a table, table_1, which has data for EACH minute and looks like:

    Code:
    +---------------------+---------+
    | date_time           | value   |
    +---------------------+---------+
    | 2015-06-05 18:00:00 | 222.663 |
    | 2015-06-05 18:01:00 | 222.749 |
    | 2015-06-05 18:02:00 | 222.957 |
    | 2015-06-05 18:03:00 | 223.063 |
    | 2015-06-05 18:04:00 | 223.117 |
    +---------------------+---------+
    I wish to fetch hourly min and max values. Something like:
    Code:
    +---------------------+---------+
    | date_time           | value   |
    +---------------------+---------+
    | 2015-06-05 18:10:00 | 200.663 |
    | 2015-06-05 18:45:00 | 222.749 |
    | 2015-06-05 19:10:00 | 190.957 |
    | 2015-06-05 19:33:00 | 400.063 |
    +---------------------+---------+
    Currently I am creating a for loop in PHP and runs a query for EACH hour. However, it is obviously not the best way to do it, and is thus VERY TIME CONSUMING. For example:

    Code:
    for($temp = $st; $temp <= $et; $temp = $temp + 60){//looping for each hour
    
     $sql1 = "select * from table_1 where value in 
     (select max(value) from table_1 
     where date_time >= '".date('Y-m-d H:i:s',$st)."' 
     and date_time < '".date('Y-m-d H:i:s',$temp)."' ) 
     and date_time >= '".date('Y-m-d H:i:s',$st)."' 
     and date_time < '".date('Y-m-d H:i:s',$temp)."' limit 1";
    
     $rs1 = mysql_query($sql1);
    
     $row1 = mysql_fetch_assoc($rs1)
     $val1 = $row1['value']; 
     $date1 = $row1['date_time'];
    
    }

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is "air code" without even a syntax check, but something like:
    Code:
    SELECT Left(Sysdate(), 13) AS hr ,Count(*) AS rc
    ,  Min(value) AS 'Min', Avg(value) AS 'Avg', Max(value) AS 'Max'
       FROM table_1
       GROUP BY Left(Sysdate(), 13)
       ORDER BY Left(Sysdate(), 13) DESC;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you need hourly data, then what you could do is have a summary table. populated by ,say a CRON job that kicks off periodically to provide that summarising. arguably it violates normalisation 'rules', but then again the art is knowing waht 'rules' could/shgould be broken and when. and static (unchanging) values such as these which can take a lot of process time are prime candidates form that.

    Another approach is to consider doing the hourly summarisation as the data is inserted into the table. depends on how the data is coming into the db, but if its being inserted row s by row and each value captured you could also use that process to write the hourly summary, assuming that the same process remains active and is aware of the previous min/max values

    l would not expect a min/max on 60 rows to be a significant resource consumer though.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •