如何无需手动修改时间，按季度或周执行SQL统计查询？

Instead of manually updating date ranges for each quarter or week, you can use PostgreSQL's built-in date functions to automatically group and filter your data. Here's how to adapt your original query for dynamic time-based aggregations:

Quarterly Aggregation #

This query will return the case count and average resolution time for each quarter in 2017, without hardcoding start/end dates:

SELECT
  -- Format quarter as a human-readable label (e.g., "Q1 2017")
  to_char(date_trunc('quarter', created_at::TIMESTAMP), '"Q"Q YYYY') AS quarter,
  COUNT(1) AS case_count,
  AVG(resolved_at::TIMESTAMP - created_at::TIMESTAMP) AS avg_resolution_time
FROM supp_cases
WHERE
  -- Filter to 2017 data (adjust year as needed)
  created_at::TIMESTAMP >= '2017-01-01'::TIMESTAMP
  AND created_at::TIMESTAMP < '2018-01-01'::TIMESTAMP
  -- Ensure resolved date falls within the same quarter as creation
  AND resolved_at::TIMESTAMP < date_trunc('quarter', created_at::TIMESTAMP) + INTERVAL '3 months'
GROUP BY date_trunc('quarter', created_at::TIMESTAMP)
ORDER BY date_trunc('quarter', created_at::TIMESTAMP);

Key Details: #

• *date_trunc('quarter', created_at)*: Truncates the creation date to the start of its quarter (e.g., 2017-02-15 becomes 2017-01-01).
• *date_trunc(...) + INTERVAL '3 months'*: Gets the first day of the next quarter, so using < ensures we include all times up to the end of the current quarter (no need to manually write 23:59:59).
• *to_char(...)*: Formats the quarter into a user-friendly string for readability.

Weekly Aggregation #

To adapt this for weekly reporting, simply replace 'quarter' with 'week' in the date functions:

SELECT
  -- Format week as start date (e.g., "2017-01-02" for the first week of 2017)
  to_char(date_trunc('week', created_at::TIMESTAMP), 'YYYY-MM-DD') AS week_start,
  COUNT(1) AS case_count,
  AVG(resolved_at::TIMESTAMP - created_at::TIMESTAMP) AS avg_resolution_time
FROM supp_cases
WHERE
  created_at::TIMESTAMP >= '2017-01-01'::TIMESTAMP
  AND created_at::TIMESTAMP < '2018-01-01'::TIMESTAMP
  -- Ensure resolved date falls within the same week as creation
  AND resolved_at::TIMESTAMP < date_trunc('week', created_at::TIMESTAMP) + INTERVAL '1 week'
GROUP BY date_trunc('week', created_at::TIMESTAMP)
ORDER BY date_trunc('week', created_at::TIMESTAMP);

Note on Week Start: #

By default, PostgreSQL uses Monday as the first day of the week. If you need to use Sunday instead, you can adjust the date_trunc behavior with SET datestyle = 'ISO, MDY'; or use extract(isodow from created_at) for ISO weeks (Monday start).

Making It Fully Dynamic (No Year Filter) #

If you want to aggregate data across all years, just remove the year-specific WHERE clauses:

SELECT
  to_char(date_trunc('quarter', created_at::TIMESTAMP), '"Q"Q YYYY') AS quarter,
  COUNT(1) AS case_count,
  AVG(resolved_at::TIMESTAMP - created_at::TIMESTAMP) AS avg_resolution_time
FROM supp_cases
WHERE
  resolved_at::TIMESTAMP < date_trunc('quarter', created_at::TIMESTAMP) + INTERVAL '3 months'
GROUP BY date_trunc('quarter', created_at::TIMESTAMP)
ORDER BY date_trunc('quarter', created_at::TIMESTAMP);

This will return results for every quarter present in your supp_cases table, with zero manual date updates required.

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