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/core";
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 "@/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
.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/core";
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 "@/generated";
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
}
}
}