# Time-series data \[Techniques for working with time-series data]

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

## Candlestick charts (bucketing)

To power a [candlestick](https://en.wikipedia.org/wiki/Candlestick_chart) or open-high-low-close chart, create a table that stores OHLC data for a specific time interval.

```ts [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.

```ts [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.

:::code-group
```graphql [GraphQL query]
{
  hourBuckets(orderBy: { id: "desc" }, limit: 48) {
    items {
      id
      open
      close
      low
      high
      average
      count
    }
  }
}
```

```sql [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.

:::code-group
```ts [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(),
}));
```

```ts [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.id,
    from: event.args.from,
    to: event.args.to,
    amount: event.args.amount,
    timestamp: event.block.timestamp, // [!code focus]
  });
});
```
:::

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

```graphql [GraphQL query]
{
  swapEvents(
    orderBy: { timestamp: "desc" }
    where: { timestamp_gt: 1712500000, timestamp_lt: 1713000000 }
  ) {
    items {
      id
      from
      to
      amount
      timestamp
    }
  }
}
```