Skip to main content
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)

​
Available Tables

TableGranularity
utils.daysOne row per day
utils.hoursOne row per hour
utils.minutesOne row per minute
utils.weeksOne row per week
utils.monthsOne row per month
utils.quartersOne row per quarter
utils.yearsOne 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.