How Can I Group Time-Series Data in MySQL by 10-Second, 30-Second, or Other Intervals?

SELECT / GROUP BY - Segments of Time (10 Seconds, 30 Seconds, etc)

In MySQL, it is possible to retrieve aggregated data from a table based on time intervals using the SELECT and GROUP BY clauses. Consider a scenario where you have a table recording samples every n seconds, with two relevant columns: time_stamp and count.

To obtain sums or averages of the count column grouped by specific time intervals, follow these steps:

1. Convert the time_stamp column to a UNIX timestamp using the UNIX_TIMESTAMP function.
2. Divide the timestamp by the desired interval. For example, to group by 10-second intervals: UNIX_TIMESTAMP(time_stamp) DIV 10.

3. Use the result from step 2 as the grouping criteria in the GROUP BY clause, as seen in the following query:

   SELECT UNIX_TIMESTAMP(time_stamp) DIV 10, SUM(count)
   FROM table
   GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 10;

This query will group the data into 10-second intervals, summing the count values within each interval.

4. If desired, adjust the boundaries between the intervals by adding an offset to the timestamp. For instance, to group by intervals from hh:mm:05 to hh:mm:35, use:

   GROUP BY (UNIX_TIMESTAMP(time_stamp) + 5) DIV 10

By leveraging the GROUP BY and UNIX_TIMESTAMP functions, you can efficiently aggregate data from time-series tables based on various time segments, providing insights into patterns and trends over time.

The above is the detailed content of How Can I Group Time-Series Data in MySQL by 10-Second, 30-Second, or Other Intervals?. For more information, please follow other related articles on the PHP Chinese website!