按周期时间汇总数据库表中机器工时：每7天统计指定时段数据

Alright, let's figure out how to aggregate those machine working hours into 7-day chunks between your specified date range. This is a common time-series aggregation task, and here's how to pull it off with SQL for most major databases:

The core approach here is to bucket each work record into its corresponding 7-day interval (aligned to your start date 2018-04-05), then group by these intervals and sum up the total working hours.

Step 1: Core Logic Breakdown #

We calculate how many days each record's date is offset from your start date, then use that to determine which 7-day block it belongs to. This ensures intervals start exactly on 2018-04-05 and repeat every 7 days after that.

Step 2: Database-Specific SQL Examples #

Below are tailored queries for popular databases—just swap out the table/column names with your actual schema:

PostgreSQL #

Use DATE_PART and arithmetic to align intervals to your start date:

SELECT
  -- Start date of the 7-day interval
  (DATE '2018-04-05' + (FLOOR((DATE_PART('day', work_date - DATE '2018-04-05')) / 7) * 7)::INTEGER) AS interval_start,
  -- End date of the interval (6 days after start)
  (DATE '2018-04-05' + (FLOOR((DATE_PART('day', work_date - DATE '2018-04-05')) / 7) * 7 + 6)::INTEGER) AS interval_end,
  SUM(work_hours) AS total_hours
FROM
  machine_work_hours -- Replace with your table name
WHERE
  work_date BETWEEN DATE '2018-04-05' AND DATE '2019-07-10'
GROUP BY
  interval_start, interval_end
ORDER BY
  interval_start;

MySQL #

Leverage DATEDIFF and DATE_SUB to compute interval boundaries:

SELECT
  -- Calculate interval start aligned to 2018-04-05
  DATE_SUB('2018-04-05', INTERVAL -(FLOOR(DATEDIFF(work_date, '2018-04-05') / 7) * 7) DAY) AS interval_start,
  -- Calculate interval end
  DATE_ADD(interval_start, INTERVAL 6 DAY) AS interval_end,
  SUM(work_hours) AS total_hours
FROM
  machine_work_hours -- Replace with your table name
WHERE
  work_date BETWEEN '2018-04-05' AND '2019-07-10'
GROUP BY
  interval_start, interval_end
ORDER BY
  interval_start;

SQL Server #

Use DATEADD and DATEDIFF to bucket records into intervals:

SELECT
  -- Start date of the 7-day interval
  DATEADD(day, DATEDIFF(day, '2018-04-05', work_date) / 7 * 7, '2018-04-05') AS interval_start,
  -- End date of the interval
  DATEADD(day, DATEDIFF(day, '2018-04-05', work_date) / 7 * 7 + 6, '2018-04-05') AS interval_end,
  SUM(work_hours) AS total_hours
FROM
  machine_work_hours -- Replace with your table name
WHERE
  work_date BETWEEN '2018-04-05' AND '2019-07-10'
GROUP BY
  DATEADD(day, DATEDIFF(day, '2018-04-05', work_date) / 7 * 7, '2018-04-05'),
  DATEADD(day, DATEDIFF(day, '2018-04-05', work_date) / 7 * 7 + 6, '2018-04-05')
ORDER BY
  interval_start;

Key Notes to Keep in Mind #

• Replace machine_work_hours, work_date, and work_hours with your actual table and column names.
• The final interval will automatically adjust to end on 2019-07-10 even if it's not a full 7 days.
• All intervals are inclusive of both their start and end dates (e.g., the first interval covers 2018-04-05 to 2018-04-11).

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