</>
sqlbeginner

Generate a Date Series

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

sql
-- PostgreSQL: generate_series for dates
SELECT d::date AS date
FROM generate_series(
  '2025-01-01'::date,
  '2025-12-31'::date,
  '1 day'::interval
) AS d;

-- Fill gaps in daily metrics
SELECT
  dates.date,
  COALESCE(m.page_views, 0) AS page_views,
  COALESCE(m.unique_visitors, 0) AS unique_visitors
FROM generate_series(
  '2025-01-01'::date,
  CURRENT_DATE,
  '1 day'
) AS dates(date)
LEFT JOIN daily_metrics m ON m.metric_date = dates.date
ORDER BY dates.date;

Use Cases

  • Filling gaps in time-series data
  • Calendar table generation
  • Reporting

Tags

#sql#date-series#generate-series#time-series

Related Snippets

Similar patterns you can reuse in the same workflow.

sqlbeginner

Generate Series Calendar Table

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

Best for: Daily revenue reports

#generate-series#calendar
sqladvanced

Recursive CTE for Hierarchical Data

Query hierarchical data like org charts, categories, and file trees using recursive CTEs.

Best for: Querying organizational hierarchies

#sql#cte
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
sqlintermediate

SQL Upsert and Merge Patterns

Insert or update records atomically using ON CONFLICT, MERGE, and database-specific upsert syntax.

Best for: Syncing data from external sources

#sql#upsert