Utilities

Time-series scaffolding tables for blockchain analytics on Dune

Dune’s utility tables provide time-series scaffolding — pre-built tables of timestamps at different granularities from Bitcoin genesis (January 3, 2009) to the current date. They are essential for creating continuous time axes in queries and dashboards.

Maintained by: Dune · Refresh: N/A · Coverage: Universal (no chain-specific data)

Get This Data

Access utility tables via API, Datashare, or the Dune App.

Available Tables

Table	Granularity
utils.days	One row per day
utils.hours	One row per hour
utils.minutes	One row per minute
utils.weeks	One row per week
utils.months	One row per month
utils.quarters	One row per quarter
utils.years	One row per year

All tables have a single column: timestamp (type: timestamp(3) with time zone), spanning from January 3, 2009 (Bitcoin genesis) to the current date.

When to Use These Tables

• Fill gaps in time-series data (ensure every day/hour has a row, even with zero activity)
• Generate date ranges for analysis windows
• Build continuous time axes for dashboard charts
• Create calendar-based aggregations

Example Queries

Fill gaps in daily DEX volume (ensures every day appears):

SELECT
  d.timestamp AS day,
  COALESCE(SUM(t.amount_usd), 0) AS daily_volume
FROM utils.days d
LEFT JOIN dex.trades t
  ON date_trunc('day', t.block_time) = d.timestamp
  AND t.blockchain = 'ethereum'
WHERE d.timestamp >= DATE '2025-01-01'
  AND d.timestamp < CURRENT_DATE
GROUP BY 1
ORDER BY 1

Hourly gas fee analysis with no gaps:

SELECT
  h.timestamp AS hour,
  COALESCE(AVG(g.tx_fee_usd), 0) AS avg_fee_usd,
  COALESCE(COUNT(g.tx_hash), 0) AS num_transactions
FROM utils.hours h
LEFT JOIN gas.fees g
  ON date_trunc('hour', g.block_time) = h.timestamp
  AND g.blockchain = 'ethereum'
WHERE h.timestamp >= NOW() - INTERVAL '48' HOUR
GROUP BY 1
ORDER BY 1

Enterprise Data Solutions

Need custom time-series utilities? Talk to our enterprise team.

Build Custom Models

Build private analytics pipelines with the dbt Connector.