Documentation
Guides
Time-series data

Time-series data

This guide describes techniques for working with time-series data in your app.

Candlestick charts (bucketing)

To power a candlestick or open-high-low-close chart, create a table that stores OHLC data for a specific time interval.

ponder.schema.ts
import { onchainTable } from "ponder";
 
export const hourBuckets = onchainTable("hour_buckets", (t) => ({
  id: t.integer().primaryKey(),
  open: t.real().notNull(),
  close: t.real().notNull(),
  low: t.real().notNull(),
  high: t.real().notNull(),
  average: t.real().notNull(),
  count: t.integer().notNull(),
}));

Then, in your indexing function, create or update the bucket record that the current event falls into.

src/index.ts
import { ponder, type Schema } from "ponder:registry";
 
const secondsInHour = 60 * 60;
 
ponder.on("Token:Swap", async ({ event, context }) => {
  const { timestamp } = event.block;
  const { price } = event.args;
 
  const hourId = Math.floor(timestamp / secondsInHour) * secondsInHour;
 
  await context.db
    .insert(hourBuckets)
    .values({
      id: hourId,
      open: price,
      close: price,
      low: price,
      high: price,
      average: price,
      count: 1,
    })
    .onConflictDoUpdate((row) => ({
      close: price,
      low: Math.min(row.low, price),
      high: Math.max(row.high, price),
      average: (row.average * row.count + price) / (row.count + 1),
      count: row.count + 1,
    }));
});

Here are GraphQL and SQL queries that return the last 48 hours of OHLC data. These queries could be used to power a chart on your frontend.

GraphQL query
{
  hourBuckets(orderBy: { id: "desc" }, limit: 48) {
    items {
      id
      open
      close
      low
      high
      average
      count
    }
  }
}
SQL query
SELECT *
  FROM "HourBucket"
  ORDER BY id DESC
  LIMIT 48;

Include block.timestamp

The simplest way to add a time dimension to your data is to include the block number or block timestamp (or both!) as a column.

ponder.schema.ts
import { onchainTable } from "ponder";
 
export const swapEvents = onchainTable("swap_events", (t) => ({
  id: t.text().primaryKey(),
  from: t.hex().notNull(),
  to: t.hex().notNull(),
  amount: t.bigint().notNull(),
  timestamp: t.bigint().notNull(),
}));
src/index.ts
import { ponder } from "ponder:registry";
import { swapEvents } from "ponder:schema";
 
ponder.on("Token:Swap", async ({ event, context }) => {
  await context.db.insert(swapEvents).values({
    id: event.log.id,
    from: event.args.from,
    to: event.args.to,
    amount: event.args.amount,
    timestamp: event.block.timestamp,
  });
});

Now, you can use the timestamp column to filter and sort data over different time intervals.

GraphQL query
{
  swapEvents(
    orderBy: { timestamp: "desc" }
    where: { timestamp_gt: 1712500000, timestamp_lt: 1713000000 }
  ) {
    items {
      id
      from
      to
      amount
      timestamp
    }
  }
}
Telemetry0.1 – 0.6