</>
sqlbeginner

Generate Series Calendar Table

Create a date calendar using generate_series for gap-free time series reporting and joins.

sql
-- Generate a calendar of dates
WITH calendar AS (
  SELECT d::date AS date
  FROM generate_series(
    '2025-01-01'::date,
    '2025-12-31'::date,
    '1 day'::interval
  ) AS d
)
SELECT
  c.date,
  COALESCE(SUM(o.amount), 0) AS daily_revenue,
  COUNT(o.id) AS order_count
FROM calendar c
LEFT JOIN orders o ON o.created_at::date = c.date
GROUP BY c.date
ORDER BY c.date;

-- Generate hourly slots
SELECT generate_series(
  '2025-01-01 00:00'::timestamp,
  '2025-01-01 23:00'::timestamp,
  '1 hour'::interval
) AS slot;

Use Cases

  • Daily revenue reports
  • Gap-free time series
  • Attendance tracking

Tags

#generate-series#calendar#time-series#reporting

Related Snippets

Similar patterns you can reuse in the same workflow.

sqlbeginner

Generate a Date Series

Create a continuous date range to fill gaps in time-series queries.

Best for: Filling gaps in time-series data

#sql#date-series
sqlbeginner

String Aggregation with GROUP BY

Concatenate grouped values into comma-separated strings using STRING_AGG with ordering and filtering.

Best for: Tag lists per item

#aggregation#string-agg
sqlintermediate

SQL Pivot and Crosstab Queries

Transform row data into columnar reports using CASE expressions, FILTER, and crosstab patterns.

Best for: Creating monthly revenue reports

#sql#pivot
pythonintermediate

Prophet Time Series Forecasting

Forecast time-series data with Facebook Prophet handling holidays, trends, and seasonality.

Best for: sales forecasting

#prophet#forecasting