How to group datetime into intervals of 3 hours in mysql?

@darrion.kuhn 

To group datetime into intervals of 3 hours in MySQL, you can use the DATE_FORMAT() and TIMESTAMPDIFF() functions.

Here's an example query:

1
2
3
4
5
6

SELECT TIMESTAMP(DATE_FORMAT(datetime_column, '%Y-%m-%d %H:00:00')) AS interval_start,
       TIMESTAMP(DATE_FORMAT(datetime_column, '%Y-%m-%d %H:00:00')) + INTERVAL 3 HOUR AS interval_end,
       COUNT(*) AS total_records
FROM your_table
GROUP BY FLOOR(TIMESTAMPDIFF(HOUR, datetime_column, '1970-01-01 00:00:00') / 3)
ORDER BY interval_start;

In this query, replace "datetime_column" with the actual name of your datetime column, and "your_table" with the name of your table.

The query uses the DATE_FORMAT() function to truncate the datetime values to the nearest hour. It then calculates the interval end time by adding 3 hours to the interval start time. The TIMESTAMPDIFF() function is used to calculate the number of hours since '1970-01-01 00:00:00' for each datetime value, and the FLOOR() function is used to divide that number by 3 to determine the interval group.

The result of the query will be a list of intervals with the start and end times, along with the total number of records in each interval. The intervals will be sorted in ascending order of interval start time.