Group MySQL Data into Arbitrarily Sized Time Buckets

How do I count the number of records in a MySQL table based on a timestamp column per unit of time where the unit of time is arbitrary?

Specifically, I want to count how many record's timestamps fell into 15 minute buckets during a given interval. I understand how to do this in buckets of 1 second, 1 minute, 1 hour, 1 day etc. using MySQL date functions, e.g.

SELECT YEAR(datefield) Y, MONTH(datefield) M, DAY(datefield) D, COUNT(*) Cnt FROM mytable GROUP BY YEAR(datefield), MONTH(datefield), DAY(datefield)

but how can I group by 15 minute buckets?

This question and answers originated from www.stackoverflow.com
Question by (4/5/2010 5:19:42 PM)

Answer

 
GROUP BY
    YEAR(datefield),
    MONTH(datefield),
    DAY(datefield),
    HOUR(datefield),
    FLOOR(MINUTE(datefield)/15)

You could alternatively say just:

SELECT FLOOR(UNIX_TIMESTAMP(datefield)/900) AS t, COUNT(*) AS cnt
FROM mytable
GROUP BY t

with the application responsible for formatting the timestamp for each 15-minute period back out to readable y/m/d/h/m. (If you needed to have local time in a crazy non-quarter-hour-aligned timezone, you'd need a hack offset though.)

Answer by
Find More Answers
Related Topics  mysql  query  time
Related Questions

  • Mysql Average on time column?

    SELECT avg( duration ) as average FROM `login`; The datatype for duration is "time", thus my value is like: 00:00:14, 00:20:23 etc I execute the query it gives me: 2725.78947368421 What is…

  • How to input the current time into a mysql table from PHP

    I'm using a mysql_query() in PHP to insert a value into a table in a database. One of the attributes of that table is "timestamp" which is supposed to be the current time that the query was made. Ho…

  • MySQL Querying database for time periods not already assigned

    I have a rather complex-seeming query that will form the basis for an online classroom scheduling tool. My challenge is to develop a method to identify which classes a user is signed up for in the s…

  • How do I difference time in MySQL query?

    There's a datetime field "time" and it's in Y-m-d H:i:s format. All the values are past values. I need to query that field diffed from current time resulting in seconds. SELECT time FROM table

  • MySql query execution time

    I am using MySQL Workbench on Windows. How do I find out the time taken to execute a query like Select * from employers where employerid > 200 Is there a statement I can enter that returns…

  • Best way to query mysql for total time worked in a day

    I'm trying to come up with the best way to add the total time an employee was clocked in. The punch types are day in, break out, break in & day out . You would think I could just select * whe…

  • combining 2 time series

    I am working with price data for futures contracts. I have data for Dec. 2010 and Mar. 2011, that I have loaded into the db. I want to generate a flat file with the time series that includes both co…

  • Query time result in MySQL w/ PHP

    Is there a way that I can get the time of a MySQL query (specifically with PHP)? The actual time it took to complete the query, that is. Something such as: Results 1 - 10 for brown. (0.11 seconds…

  • sql query date time

    i have a datestamp field in my table structure. using sql, i want to find any user who registered in yesterdays date using these time range. eg: 2010-02-06 14:00:00 2010-02-07 10:00:00 i will…

  • mysql query execution time | reduce query

    I have a query which let me to change users order. here is my query: update test set orderID = case orderID when (select orderID from ( select * from test where orderID > ( select …