Prompt InFlow
CodingDatabaseIntermediate30 minSaves 30 minutes

Hourly Event Aggregation with Time Zone and Empty Buckets

Data engineers can implement time-zone aware hourly event aggregation, including empty buckets, using this PostgreSQL SQL workflow for clearer analytics dashboards.

This workflow guides data engineers through creating an hourly time-bucketed aggregation of event data using PostgreSQL's generate_series. It addresses time zone complexities and ensures all hours are represented, even those without events, for comprehensive analytics.

ChatGPTGeminiClaude

READY-TO-USE PROMPT

Copy Prompt

prompt.txt
Role: Database Engineer specializing in data warehousing and analytics.

Context: We are building an analytics dashboard that requires a granular view of event occurrences over time. Specifically, we need hourly counts of events, but with a critical requirement: all hours within a specified range must be represented, even if no events occurred in that hour. This ensures a complete time series for visualization. Additionally, event timestamps are stored in UTC, but the reporting needs to align with a specific local time zone. The solution must be robust for PostgreSQL.

Task: Provide a complete, runnable PostgreSQL SQL solution that performs hourly time-bucketed aggregation of events. Your output must include:

1.  **DDL (Data Definition Language)** for a sample event table. This table should minimally include an `event_id` (primary key) and `event_timestamp` (timestamp with time zone).
2.  **SQL Query** to perform the aggregation. This query must:
    *   Aggregate events hourly.
    *   Utilize `generate_series` to ensure all hours within a defined start and end range are present in the output, including those with zero events.
    *   Correctly handle time zone conversion from the stored UTC `event_timestamp` to the `{{timezone}}` specified.
    *   Output the start of each hourly bucket and the count of events for that hour.
3.  **Expected Plan Notes**: Briefly explain the expected execution plan, especially concerning `generate_series` and joins, and how it impacts performance.
4.  **Index Recommendation**: Suggest an appropriate index for the sample table to optimize this specific query.
5.  **Test Data**: Provide `INSERT` statements for at least 10 sample events that demonstrate various scenarios, including events within the same hour, events across different hours, and gaps where no events occur. The test data should span a few days to illustrate the `generate_series` range.

Constraints:

*   Use standard PostgreSQL syntax.
*   The `generate_series` function is mandatory for creating the time buckets.
*   The solution must dynamically adjust to the `{{timezone}}` placeholder for output.
*   The query should be performant on tables with millions of records.
*   The output format for the aggregated data should be `hourly_bucket_start` (timestamp with time zone) and `event_count` (integer).
*   The `{{event_table}}` placeholder should be used for the table name.

Output: Deliver the DDL, the aggregation query, plan notes, index recommendation, and test data as outlined above. Ensure all components are clearly labeled and runnable.

Estimated results

DifficultyIntermediate
Setup time30 min
Time saved30 minutes
Best modelsChatGPT, Gemini, Claude
Best audienceData Analytics, Software Development

Editor's note

Why this prompt matters

Building analytics dashboards often requires presenting event data over time. A common challenge arises when visualizing hourly trends: if no events occur in a particular hour, that hour might simply disappear from the dataset, leading to incomplete or misleading charts. This issue is compounded by the need to reconcile event timestamps, typically stored in UTC, with a specific local reporting time zone. Data engineers frequently encounter this when preparing data for business intelligence tools that expect a continuous time series.

This workflow addresses these specific data preparation hurdles. It provides a structured approach for generating a complete hourly time series, ensuring every hour within a defined period is present, even those with zero events. By explicitly handling time zone conversions, it guarantees that the aggregated data aligns correctly with the intended local reporting context. This is crucial for accurate historical analysis and real-time monitoring, preventing misinterpretations caused by missing data points or time zone discrepancies.

Engineers should use this pattern when constructing time-based reports where a continuous axis is non-negotiable. Whether tracking user activity, system performance metrics, or business transactions, a consistent hourly view is fundamental for reliable trend analysis and anomaly detection.

Anatomy

Prompt engineering breakdown

Role

You are a Database Engineer specializing in data warehousing and analytics.

Context

We are building an analytics dashboard that requires hourly event counts, including all hours within a specified range (even empty ones), and correct time zone conversion from UTC to a local time zone for PostgreSQL.

Goal

Provide a complete, runnable PostgreSQL SQL solution for hourly time-bucketed event aggregation, ensuring all hours are represented and time zones are handled correctly, including DDL, the aggregation query, plan notes, index recommendation, and test data.

Constraints

The solution must use standard PostgreSQL syntax, explicitly use `generate_series` for time buckets, dynamically adjust to `{{timezone}}`, be performant for large datasets, and adhere to a specific output format for aggregated data.

Output format

The output must include DDL for a sample table, the SQL aggregation query, expected execution plan notes, an index recommendation, and test data `INSERT` statements, all clearly labeled.

Why this structure works

This prompt employs strong role priming, setting clear expectations for the model's expertise. Explicit constraints on syntax, function usage (generate_series), and performance ensure the solution meets specific technical requirements. The highly structured output format guarantees all necessary components (DDL, query, plan notes, index, test data) are provided, making the response immediately actionable.

Pick your version

Prompt variations

BeginnerWorks with any model

For users new to SQL time series analysis or those needing a basic hourly count without deep performance tuning or specific time zone complexities.

prompt.txt
As a SQL instructor, provide a basic PostgreSQL solution to count events hourly. I need a table `{{event_table}}` with `event_timestamp` (UTC) and `event_id`. The query should aggregate events into hourly buckets for a range from `{{start_date}}` to `{{end_date}}`, ensuring all hours are present, even if no events occurred. Convert UTC timestamps to `{{timezone}}` for the output. Include DDL, sample `INSERT` statements, and the aggregation query.
ProfessionalBest with claude

For experienced data engineers requiring a production-ready, performant solution that accounts for all specified technical requirements, including detailed plan analysis and indexing.

prompt.txt
Role: Database Engineer specializing in data warehousing and analytics. Context: We are building an analytics dashboard that requires a granular view of event occurrences over time. Specifically, we need hourly counts of events, but with a critical requirement: all hours within a specified range must be represented, even if no events occurred in that hour. This ensures a complete time series for visualization. Additionally, event timestamps are stored in UTC, but the reporting needs to align with a specific local time zone. The solution must be robust for PostgreSQL. Task: Provide a complete, runnable PostgreSQL SQL solution that performs hourly time-bucketed aggregation of events. Your output must include: 1. DDL (Data Definition Language) for a sample event table. This table should minimally include an `event_id` (primary key) and `event_timestamp` (timestamp with time zone). 2. SQL Query to perform the aggregation. This query must: Aggregate events hourly. Utilize `generate_series` to ensure all hours within a defined start and end range are present in the output, including those with zero events. Correctly handle time zone conversion from the stored UTC `event_timestamp` to the `{{timezone}}` specified. Output the start of each hourly bucket and the count of events for that hour. 3. Expected Plan Notes: Briefly explain the expected execution plan, especially concerning `generate_series` and joins, and how it impacts performance. 4. Index Recommendation: Suggest an appropriate index for the sample table to optimize this specific query. 5. Test Data: Provide `INSERT` statements for at least 10 sample events that demonstrate various scenarios, including events within the same hour, events across different hours, and gaps where no events occur. The test data should span a few days to illustrate the `generate_series` range. Constraints: Use standard PostgreSQL syntax. The `generate_series` function is mandatory for creating the time buckets. The solution must dynamically adjust to the `{{timezone}}` placeholder for output. The query should be performant on tables with millions of records. The output format for the aggregated data should be `hourly_bucket_start` (timestamp with time zone) and `event_count` (integer). The `{{event_table}}` placeholder should be used for the table name. Output: Deliver the DDL, the aggregation query, plan notes, index recommendation, and test data as outlined above. Ensure all components are clearly labeled and runnable.
Short VersionBest with chatgpt

When a concise request is preferred by experienced users who understand the underlying technical requirements and desired output structure.

prompt.txt
Generate a PostgreSQL SQL solution for hourly event aggregation. Include DDL for an `{{event_table}}` with `event_timestamp` (UTC) and `event_id`. The aggregation query must use `generate_series` to cover all hours from `{{start_time}}` to `{{end_time}}`, convert `event_timestamp` from UTC to `{{timezone}}`, and count events per hour, showing zero for empty buckets. Provide sample `INSERT` statements to demonstrate usage and an index recommendation for query performance on larger datasets.
EnterpriseBest with gemini

For enterprise-level applications where data accuracy, compliance, scalability, and maintainability are critical requirements for analytical reporting.

prompt.txt
As a Lead Data Architect, design a robust and auditable PostgreSQL SQL solution for critical hourly event aggregation within our enterprise analytics platform. The solution must ensure data integrity for all reporting periods, reflecting all hours from `{{reporting_start_utc}}` to `{{reporting_end_utc}}`, even those without recorded events. Implement precise time zone conversion from stored UTC `event_timestamp` to the `{{business_timezone}}` for stakeholder reports. Provide DDL for `{{event_data_table}}`, the aggregation query, detailed execution plan considerations for large-scale data volumes, and a recommended indexing strategy to meet service level agreements for dashboard responsiveness. Include comprehensive test data demonstrating edge cases for validation and compliance auditing.

What you'll get

Expected output

-- DDL for sample event table CREATE TABLE events ( event_id SERIAL PRIMARY KEY, event_timestamp TIMESTAMP WITH TIME ZONE NOT NULL );

-- SQL Query for hourly aggregation with empty buckets and time zone conversion WITH hourly_series AS ( SELECT generate_series( '2023-10-26 00:00:00'::timestamp with time zone AT TIME ZONE 'UTC', '2023-10-28 23:00:00'::timestamp with time zone AT TIME ZONE 'UTC', '1 hour'::interval ) AS hourly_bucket_utc ), local_hourly_series AS ( SELECT hourly_bucket_utc, (hourly_bucket_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AS hourly_bucket_start FROM hourly_series ) SELECT lhs.hourly_bucket_start, COALESCE(COUNT(e.event_id), 0) AS event_count FROM local_hourly_series lhs LEFT JOIN events e ON e.event_timestamp >= lhs.hourly_bucket_utc AND e.event_timestamp < (lhs.hourly_bucket_utc + INTERVAL '1 hour') GROUP BY lhs.hourly_bucket_start ORDER BY lhs.hourly_bucket_start;

-- Expected Plan Notes The query will likely start by materializing the generate_series output, creating a temporary table or a CTE of all hourly buckets. This series is then joined with the events table using a LEFT JOIN. For optimal performance, the join condition e.event_timestamp >= lhs.hourly_bucket_utc AND e.event_timestamp < (lhs.hourly_bucket_utc + INTERVAL '1 hour') will benefit from an index on event_timestamp. The GROUP BY clause will then aggregate the counts. The COALESCE function ensures that hours with no matching events from the LEFT JOIN correctly show a count of 0 instead of NULL. The time zone conversion happens early in the CTEs, ensuring the join is performed on UTC timestamps, which is efficient given the event_timestamp is stored in UTC.

-- Index Recommendation CREATE INDEX idx_events_event_timestamp ON events (event_timestamp);

-- Test Data INSERT INTO events (event_timestamp) VALUES ('2023-10-26 00:15:00+00'), -- Hour 00, Day 1 ('2023-10-26 00:45:00+00'), -- Hour 00, Day 1 ('2023-10-26 01:05:00+00'), -- Hour 01, Day 1 ('2023-10-26 01:20:00+00'), -- Hour 01, Day 1 ('2023-10-26 03:30:00+00'), -- Hour 03, Day 1 (gap in 02) ('2023-10-27 10:00:00+00'), -- Hour 10, Day 2 ('2023-10-27 10:59:00+00'), -- Hour 10, Day 2 ('2023-10-27 11:15:00+00'), -- Hour 11, Day 2 ('2023-10-28 22:00:00+00'), -- Hour 22, Day 3 ('2023-10-28 22:30:00+00'); -- Hour 22, Day 3

Under the hood

Why this prompt works

This prompt is effective because it employs several key prompt engineering techniques. First, role priming establishes the persona of a "Database Engineer specializing in data warehousing and analytics." This sets a clear expectation for the depth and technical accuracy required in the response, guiding the model to produce a solution that reflects expert-level knowledge rather than a superficial answer.

Second, explicit constraints are used extensively. The prompt mandates specific requirements like using generate_series, handling time zone conversion to a placeholder, including empty buckets, and specifying the output format. These constraints narrow the solution space, preventing generic responses and ensuring the output directly addresses the problem's nuances. For instance, requiring generate_series forces a specific, efficient PostgreSQL idiom for time series generation.

Finally, structured output is enforced by explicitly requesting DDL, the SQL query, expected plan notes, index recommendations, and test data, each clearly labeled. This multi-part structure acts as a form of scaffolding, breaking down a complex problem into manageable components. It ensures a comprehensive and immediately runnable solution, far more useful than a single query. This structured approach guarantees that all necessary elements for implementation and validation are present, making the output directly actionable for a data engineer.

Model fit

Best AI models for this prompt

Claude

Claude models excel at understanding complex SQL requirements and generating syntactically correct, idiomatic PostgreSQL. Its strength lies in interpreting the intent behind "empty buckets" and "time zone handling" to produce a generate_series solution that often requires minimal adjustment. However, verify the exact AT TIME ZONE usage, as slight variations can occur depending on the specific model version. See the full Claude hub for deeper guidance.

ChatGPT

ChatGPT models are generally proficient with SQL generation and can produce functional code for this aggregation task. They typically handle generate_series and joins well. Pay close attention to the time zone conversion logic and ensure it aligns precisely with PostgreSQL's AT TIME ZONE behavior, as it sometimes defaults to less explicit conversion methods that might not be portable. See the full ChatGPT hub for deeper guidance.

Gemini

Gemini models are capable of generating the required SQL and understanding the need for generate_series to fill gaps. They often provide clear explanations for the generated code. Double-check the precise syntax for generate_series parameters, especially the interval and start/end bounds, as minor inconsistencies can sometimes appear. Validate the time zone conversion logic for accuracy in your specific PostgreSQL environment. See the full Gemini hub for deeper guidance.

When to use

  • When visualizing time-series data where gaps in event occurrences would distort trends or make analysis difficult.
  • For analytics dashboards that require a complete hourly timeline, ensuring no missing data points for any hour within a reporting period.
  • When calculating hourly metrics where zero values for absent data points are critical for accurate statistical analysis.
  • To analyze event frequency patterns across specific daily or weekly periods, including periods of inactivity.
  • When migrating analytics from systems that automatically include empty time buckets, ensuring consistency.

When not to use

  • When only interested in hours with actual event occurrences, to minimize the size of the result set and processing.
  • For very long time ranges (e.g., several months or years) at hourly granularity, due to the potentially massive output and performance implications.
  • If the underlying database system is not PostgreSQL, as generate_series syntax and time zone handling may differ significantly.
  • When event timestamps are already stored and reported in the exact desired local time zone, making conversion unnecessary.

Get more from it

Pro tips

  • 1

    Limit `generate_series` range: Keep the start and end timestamps tight to avoid generating excessive empty hours, which can strain memory and processing.

  • 2

    Tune `work_mem`: For large `generate_series` outputs, increasing `work_mem` can prevent disk spills during hash joins, improving overall query speed.

  • 3

    Index `event_timestamp`: Ensure a B-tree index on `event_timestamp` for efficient filtering and sorting, crucial for large event tables.

  • 4

    Partition large tables: Consider partitioning the event table by date or month to reduce the data scanned for specific date ranges.

  • 5

    Use CTEs for clarity: Structure complex aggregation logic with Common Table Expressions to improve readability and maintainability of the query.

  • 6

    Parameterize `timezone`: Always pass the `{{timezone}}` dynamically, preventing hardcoding and allowing flexible reporting for different regions.

  • 7

    Test with varying data density: Validate the query against datasets with both dense events and significant gaps to confirm correct zero-fill behavior.

Don't ship this

Common mistakes

  • ✗ Not converting `event_timestamp` to the local time zone *before* bucketing, leading to incorrect hourly groupings.

    Fix — Convert `event_timestamp` to `{{timezone}}` using `AT TIME ZONE` prior to `date_trunc('hour', ...)`, ensuring local time alignment.

  • ✗ Omitting `COALESCE` for the aggregated `COUNT` function, leading to `NULL` for hours without events instead of zero.

    Fix — Wrap `COUNT(e.event_id)` with `COALESCE(COUNT(e.event_id), 0)` to ensure zero is displayed for all empty hourly buckets.

  • ✗ Defining an excessively broad `generate_series` range, creating many unnecessary empty hourly buckets and impacting performance.

    Fix — Calculate `generate_series` start and end points precisely based on the required reporting window, perhaps from input parameters.

  • ✗ Not indexing `event_timestamp` on the event table, causing slow filters and joins on large datasets.

    Fix — Create a B-tree index on `event_timestamp` to optimize filtering for specific time ranges efficiently.

  • ✗ Using an `INNER JOIN` instead of a `LEFT JOIN` with `generate_series`, which discards hours without events.

    Fix — A `LEFT JOIN` from `generate_series` to the aggregated events ensures all time buckets are preserved in the output.

  • ✗ Neglecting to set the correct `timezone` session variable for local testing verification, leading to misleading results.

    Fix — Set `SET timezone = '{{timezone}}';` at the start of your session to verify local time zone behavior during development.

People also ask

Frequently asked questions

Q.Can this approach be adapted for different time granularities, like daily or weekly?

Yes, adjust generate_series to create daily or weekly intervals, and change date_trunc('hour', ...) to date_trunc('day', ...) or date_trunc('week', ...). Ensure the series step aligns with the desired granularity for accurate bucketing.

Q.How does this perform on event tables with millions of records?

Performance depends on the time range, event_timestamp index, and work_mem settings. For very large tables, partitioning can help. The generate_series join is typically efficient if the event table's time range is filtered first.

Q.What if I need to aggregate by additional dimensions, like `user_id` or `event_type`?

Extend the aggregation by adding GROUP BY clauses for the extra dimensions. You'll need to join generate_series with a subquery that pre-aggregates events by hour *and* those dimensions, then LEFT JOIN to fill missing time buckets per dimension.

Q.Is `generate_series` the only way to fill missing time buckets?

In PostgreSQL, generate_series is the standard and most efficient method for creating arbitrary time sequences. Other database systems might use recursive CTEs or specific date functions, but for PostgreSQL, generate_series is preferred.

Q.Will this handle daylight saving time changes correctly?

Yes, PostgreSQL's timestamp with time zone type and AT TIME ZONE conversions automatically handle daylight saving time transitions. The hourly buckets will reflect the local time zone's actual hourly progression, including the hour that repeats or is skipped.

Q.Can I use this for a `DATE` type column instead of `TIMESTAMP WITH TIME ZONE`?

While generate_series can work with DATE types, handling time zones and precise hourly bucketing becomes more complex. For event counts where specific hours matter, TIMESTAMP WITH TIME ZONE is recommended for accuracy and flexibility.

Version 1.0Last reviewed July 20, 2026
Reviewed by PromptInFlow Editorial Team