Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    12

    Question Unanswered: Aggregation < than one hour

    Hi all,
    I've got a table structured as follow and it's often filled with with new data each minute. Is it possible with a single query to retrieve data aggregation groupped by 15 minutes and half an hour ?? I can use GROUP BY SUBSTR(date,1,13) to get hourly aggregation but I really don't know how to get it with a lesser time.

    Table structure
    CREATE TABLE `test` (
    `date` datetime NOT NULL ,
    `value` float NOT NULL
    ) TYPE=MyISAM;

    Sample data
    +---------------------+------+
    | date | value |
    +---------------------+------+
    | 2004-11-01 00:01:00 | 2 |
    | 2004-11-01 00:02:00 | 1 |
    | 2004-11-01 00:03:00 | 6 |
    .......
    | 2004-11-01 00:13:00 | 5 |
    | 2004-11-01 00:14:00 | 7 |
    | 2004-11-01 00:15:00 | 7 |
    | 2004-11-01 00:16:00 | 6 |
    ......
    | 2004-11-01 00:30:00 | 4 |
    | 2004-11-01 00:31:00 | 3 |
    | 2004-11-01 00:32:00 | 3 |
    +---------------------+------+

    desired result
    | 2004-11-01 00:15:00 | 89|
    | 2004-11-01 00:30:00 | 76|
    | 2004-11-01 00:45:00 | 67|
    | 2004-11-01 01:00:00 | 27|

    Thank you very much!
    Paolo Saudin

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select from_unixtime(900*floor((unix_timestamp(`date`)+89 9)/900)) as quarterhour
    , sum(`value`) as sumvalue
    from `test`
    group by quarterhour
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    12

    Smile

    Thank you very much !
    Regard,
    Paolo Saudin

Posting Permissions

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