Pull token flows for a specific day

Fetch date-bounded token and protocol activity from logs without standing up an indexer. Use the result as evidence or eval input.

Goal

Pull decoded token transfers for one UTC window without standing up an indexer. The result keeps the block bounds beside each row so a later review or eval can see exactly what range was scanned.

When to use this

Use this for transfers, inflows, outflows, whale activity, policy evidence, or accounting data from logs when a bounded RPC scan is enough.

Inputs to change

  • $transport selects the RPC endpoint.
  • The token address is the PENDLE ERC-20 contract.
  • The UTC start and end timestamps are hardcoded in the two block_at calls.
  • The event signature is Transfer(address indexed from, address indexed to, uint256 value).
  • format_units(value, 18) assumes the token has 18 decimals.

What the query does

resolve UTC window -> find inclusive block bounds -> pull logs -> decode fields -> return ordered rows

Query

1
-- PENDLE transfers on 2024-01-08 UTC.
2
-- Resolve the UTC start and end instants to inclusive block bounds.
3
WITH bounds AS (
4
SELECT
5
block_at(
6
$transport,
7
'2024-01-08 00:00:00+00'::TIMESTAMPTZ,
8
'at_or_after'
9
) AS from_block,
10
block_at(
11
$transport,
12
'2024-01-08 23:59:59+00'::TIMESTAMPTZ,
13
'at_or_before'
14
) AS to_block
15
),
16
transfer_logs AS (
17
SELECT
18
bounds.from_block,
19
bounds.to_block,
20
logs.block_number,
21
logs.transaction_hash,
22
logs.log_index,
23
logs."from",
24
logs."to",
25
logs.value
26
FROM bounds
27
CROSS JOIN get_logs(
28
$transport,
29
'0x808507121B80c02388fAd14726482e061B8da827'::ADDRESS,
30
'event Transfer(address indexed from, address indexed to, uint256 value)',
31
bounds.from_block,
32
bounds.to_block
33
) AS logs
34
)
35
SELECT
36
from_block,
37
to_block,
38
block_number,
39
transaction_hash,
40
"from",
41
"to",
42
format_units(value, 18) AS pendle_amount
43
FROM transfer_logs
44
ORDER BY block_number, log_index;
Notebook ready in readonly mode.

Read the output

Each row is one decoded transfer log. from_block and to_block repeat on every row so exported results preserve the scan bounds.

from_block | to_block | block_number | transaction_hash | from | to | pendle_amount
-----------|----------|--------------|------------------|------|----|--------------
...        | ...      | ...          | 0x...            | ...  | ...| ...

Functions used in this guide