Documentation
Indexing
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 { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  HourBucket: p.createTable({
    id: p.int(), // Unix timestamp of the start of the hour.
    open: p.float(),
    close: p.float(),
    low: p.float(),
    high: p.float(),
    average: p.float(),
    count: p.int(),
  }),
}));

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 "@/generated";
 
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.HourBucket.upsert({
    id: hourId,
    create: { open: price, close: price, low: price, high: price, average: price, count: 1 },
    update: ({ current }) => ({
      close: price,
      low: current.low > price ? price : current.low,
      high: current.high < price ? price : current.high,
      average: (current.average * current.count + price) / current.count + 1,
      count: current.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
{
  HourBucket(
    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 { createSchema } from "@ponder/core";
 
export default createSchema((p) => ({
  SwapEvent: p.createTable({
    id: p.string(),
    from: p.hex(),
    to: p.hex(),
    amount: p.bigint(),
    timestamp: p.bigint()
  })
}));
src/index.ts
import { ponder } from "@/generated";
 
ponder.on("Token:Swap", async ({ event, context }) => {
  await context.db.SwapEvent.create({
    id: event.log.id,
    data: {
      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
{
  SwapEvent(
    orderBy: { timestamp: "desc" },
    where: { timestamp_gt: 1712500000, timestamp_lt: 1713000000 },
  ) {
    items {
      id
      from
      to
      amount
      timestamp
    }
  }
}
Call tracesAPI functions