如何让MySQL按6:30-7:30这类半小时区间统计数据？

Got it, let's solve this half-hour grouping problem efficiently—no messy range checks needed. Here's how to get that clean 6:30 - 7:30 style output you want, whether your time is stored as a datetime or integer timestamp.

For Datetime Columns #

The core idea is to calculate which 30-minute window each timestamp falls into, then format that window into your desired string. Here's the full query:

SELECT
  -- Format the half-hour interval as "start - end"
  CONCAT(
    DATE_FORMAT(
      DATE_ADD(date_time, INTERVAL -(MINUTE(date_time) % 30) MINUTE),
      '%H:%i'
    ),
    ' - ',
    DATE_FORMAT(
      DATE_ADD(date_time, INTERVAL (30 - MINUTE(date_time) % 30) MINUTE),
      '%H:%i'
    )
  ) AS Hourly,
  SUM(data_count) AS Qty
FROM your_table_name -- Replace with your actual table name
GROUP BY
  DATE(date_time), -- Group by date to avoid mixing intervals across days (e.g., 23:30 - 00:30)
  FLOOR((HOUR(date_time) * 60 + MINUTE(date_time)) / 30) -- Unique key for each half-hour window
ORDER BY
  DATE(date_time),
  FLOOR((HOUR(date_time) * 60 + MINUTE(date_time)) / 30);

Breakdown of the Logic: #

• MINUTE(date_time) % 30: Gets the remainder when the minute value is divided by 30 (e.g., 34 minutes → 4, 22 minutes → 22).
• DATE_ADD(..., INTERVAL -(remainder) MINUTE): Shifts the timestamp back to the start of its half-hour window (12:34 → 12:30, 12:22 → 12:00).
• DATE_ADD(..., INTERVAL (30 - remainder) MINUTE): Shifts forward to the end of the window (12:34 → 13:30, 12:22 → 12:30).
• The GROUP BY uses a numeric key (FLOOR(total_minutes / 30)) to group all timestamps in the same 30-minute block, which is far more efficient than writing multiple WHERE clauses for each interval.

For Integer Timestamp Columns #

If your time is stored as an integer (Unix timestamp), just convert it to a datetime first using FROM_UNIXTIME(), then apply the same logic:

SELECT
  CONCAT(
    DATE_FORMAT(
      FROM_UNIXTIME(int_timestamp) - INTERVAL (MINUTE(FROM_UNIXTIME(int_timestamp)) % 30) MINUTE,
      '%H:%i'
    ),
    ' - ',
    DATE_FORMAT(
      FROM_UNIXTIME(int_timestamp) + INTERVAL (30 - MINUTE(FROM_UNIXTIME(int_timestamp)) % 30) MINUTE,
      '%H:%i'
    )
  ) AS Hourly,
  SUM(data_count) AS Qty
FROM your_table_name
GROUP BY
  DATE(FROM_UNIXTIME(int_timestamp)),
  FLOOR((HOUR(FROM_UNIXTIME(int_timestamp)) * 60 + MINUTE(FROM_UNIXTIME(int_timestamp))) / 30)
ORDER BY
  DATE(FROM_UNIXTIME(int_timestamp)),
  FLOOR((HOUR(FROM_UNIXTIME(int_timestamp)) * 60 + MINUTE(FROM_UNIXTIME(int_timestamp))) / 30);

Example Output with Your Sample Data #

Using your 6 rows all timestamped at ~12:34, this query would return:

| Hourly         | Qty |
|----------------|-----|
| 12:30 - 13:30  | 6   |

This method is efficient because it leverages arithmetic to compute grouping keys instead of multiple conditional checks, and it plays nicely with indexes on your time column if you have them.

内容的提问来源于stack exchange，提问作者xtranghero