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
| 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 |
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
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
